Open aliml92 opened 1 year ago
Ignoring the issue of potentially having nulls in arrays, you can make sqlc use a given type by doing a type cast in your SQL:
coalesce(array_agg(b.title)::text[], array[]::text[])::text[] as book_list
this will give you a []string which according to #185 it should not do, because arrays can have NULLs.
However, it seems your book titles can never be null anyway, because you have a not null on books.title, so that shouldn't be a problem. (if there is some instance where you need to filter out NULLs from your aggregate, you can probably use the FILTER
keyword with your aggregate)
btw I think array_agg() will be an empty array if there are no books, so it will never be NULL and you don't need the COALESCE:
array_agg(b.title)::text[]
Hi, I'm having similar issue - override interval
to pgtype.Interval
,
my query looks like this:
-- name: GetResultsRow :many
WITH best_lap_times AS (
SELECT
ss.best_lap_time,
ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY ss.best_lap_time ASC) AS lap_rank
FROM session_statistics AS ss
JOIN user u ON u.id = ss.user_id
WHERE s.layout_id =$1
)
SELECT
best_lap_time
FROM best_lap_times
WHERE lap_rank = 1
ORDER BY best_lap_time ASC;
ss.best_lap_time
in PostgreSQL have type interval, but SQLC generates struct like this:
type GetResultsRow struct {
BestLapTime int64 `json:"best_lap_time"`
}
sqlc.yaml looks like this,
version: "2"
overrides:
go:
overrides:
- db_type: "interval"
engine: "postgresql"
go_type:
import: "github.com/jackc/pgtype"
package: "pgtype"
type: "Interval"
and I have error during run code: can't scan into dest[4]: unable to assign to *int64
.
Am I doing something wrong that the library doesn't recognize the right type?
For what it's worth, I'd like this as well: the ability to arbitrarily override the types of columns in queries. I frequently construct ad-hoc jsonb results to efficiently query for sets of data, and would love to shift the unmarshalling of these results deeper into the stack.
What do you want to change?
At this moment, overriding the calculated column type is not possible. For example, I have the following
query.sql
:Generated
query.sql.go
looks like the following:For the calculated
book_list
column I wanted to get[]sql.NullString
, but it is givinginterface{}
. This behavior is stated on185 which is still an open issue.
It sounds a very hacky solution, but it can be solved by allowing type override of calculated columns as well. Something like this:
And I assume
sqlc
would figure out this is an array type from thearray_agg()
function name.What database engines need to be changed?
PostgreSQL
What programming language backends need to be changed?
Go