Handle PostgreSQL system columns

[ndrpnt]

Version

1.14.0

What happened?

sqlc currently errors out with column "…" does not exist when referencing a PostgreSQL system column (tableoid, xmin, cmin, xmax, cmax or ctid) in an SQL query.

Example db-fiddle

Not sure if this should be a bug report or a feature request.

Relevant log output

column "ctid" does not exist

Database schema

CREATE TABLE test (
  id INT
);

SQL queries

SELECT ctid FROM test;

Configuration

version: 2
sql:
- schema: sql/schema.sql
  queries: sql/queries.sql
  engine: postgresql
  gen:
    go:
      out: internal/sql

Playground URL

https://play.sqlc.dev/p/0a3172295b4a8b1a7dd1f78f56a864425724d1d5118c73a45f2fd313733c6910

What operating system are you using?

No response

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

v1.23.0 is out now, which includes this fix

[smonv]

@kyleconroy I tried this feature with 1.25 but still got column does not exist error.

https://play.sqlc.dev/p/8761c174f69f3c2e47a581c049c662eb60cf481160c46b491a5ed80b2f3dd043

[swallowstalker]

after spending some time looking into the problem, I tested it using sql.database.managed set to true, and it's working. turns out that the pg config Analyzer is doing the job of getting pg system columns, and with default config of sql.database.managed set to false, then the pg config Analyzer is not initialized since there is no db to look for system columns. the solution (maybe) is to set list of hardcoded system columns if pg config Analyzer is not set, what do you think?

related docs

example config file

version: "2"
servers:
  - engine: postgresql
    uri: "postgres://user:pass@localhost:5432/dbname?sslmode=disable"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "querytest"
        out: "go"
        sql_package: "pgx/v5"
    database:
      managed: true