Postgresql stored columns, and overrides

[zeroflaw]

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_type specifier "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?

[kyleconroy]

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.

I will also need to ignore this column so its not included in the generated structs.

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.

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:

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.