Repository navigation
Description
Hello, I've been trying out sqlc, I discovered a couple of bugs, when looking if I could replace my current DB layer with a sqlc generated one. As I much prefer writing sql.
Apart from the need for #276 which I will be watching. The only outstanding issues are related to the way I must handle timestamps.
Keeping nano timestamp precision with Postgresql requires first storing that data into an bigint, I then use a computed column, this is simply for readability when querying the tables manually. A sample can be seen below: (i've ** the important line)
CREATE TABLE trades.currency_rates (
timestamp_nanos BIGINT PRIMARY KEY,
**timestamp TIMESTAMP GENERATED ALWAYS AS (to_timestamp(timestamp_nanos/1000000000.0)) STORED,**
base CHAR(10) NOT NULL,
quote CHAR(10) NOT NULL
);
This causes the following error
schema.sql:1:1: syntax error at or near "("
If I removed the STORED column, everything works as expected.
I will also need to ignore this column so its not included in the generated structs. Would you consider allowing overrides to achieve this with something like
"overrides": [ { "column": "*.timestamp", "filter": "ignore" } ]
The other bug I found was related to overrides which displays this error
error parsing sqlc.json: Package override
go_typespecifier "time.Time" is not the proper format, expected 'package.type', e.g. 'github.com/segmentio/ksuid.KSUID'
"overrides": [ { "column": "currency_rates.timestamp_nanos", "go_type": "time.Time" } ]
What i actually need to be able to do is use a wildcard (match any table) to automatically convert a data type using a function, such as:
"overrides": [ { "column": "*.timestamp_nanos", "go_function": "time.Unix(0, sqlc.(TimestampNanos)).UTC()" } ]
If I were to spend time looking to implement these features, would this be something you would consider merging?
Activity
kyleconroy commentedon Jan 29, 2020
sqlc relies on https://github.com/lfittl/pg_query_go, which currently uses the PostgreSQL 10 parser. Generated columns were added in PostgreSQL 12, hence the reason you're getting a syntax error. I've opened an issue to get the parser upgrades to version 12.
There are currently no plans to do column filtering via overrides. I understand that it's a problem, but the best way to solve it right now is to explicitly list the columns you want to return in your queries.
If you need to do custom conversion logic, sqlc expects you to create your own struct that implements Scanner and Valuer interface, such as sql.NullBool and others.
As for applying the override to all columns with a given name, that is something that could be added. No timeline on when support for that would land. In the meantime you would just need to add an override for each table.