Skip to content

Handle PostgreSQL system columns #1745

New issue
New issue
Closed
#2871
Closed
Handle PostgreSQL system columns#1745
#2871
Labels
📚 postgresql🔧 golanganalyzerbugSomething isn't working

Description

@ndrpnt
ndrpnt
opened on Jul 14, 2022

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

Activity

  1. ndrpnt
    added
    bugSomething isn't working
    triageNew issues that hasn't been reviewed
    on Jul 14, 2022
  2. kyleconroy
    added
    📚 postgresql
    🔧 golang
    on Aug 20, 2022
  3. kyleconroy
    removed
    triageNew issues that hasn't been reviewed
    on Aug 28, 2022
  4. kyleconroy
    added
    analyzer
    on Sep 26, 2023
  5. andrewmbenton
    added a commit that references this issue on Oct 16, 2023

    test: Add a test case for #1745

    49091fb
  6. kyleconroy
    closed this as completedin #2871on Oct 18, 2023
  7. kyleconroy
    added a commit that references this issue on Oct 18, 2023

    feat(postgresql): Support system columns on tables (#2871)

    f514135

  8. kyleconroy commented on Oct 25, 2023

    @kyleconroy
    kyleconroy
    on Oct 25, 2023
    Collaborator

    v1.23.0 is out now, which includes this fix

  9. smonv commented on Jun 7, 2024

    @smonv
    smonv
    on Jun 7, 2024

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

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

  10. swallowstalker commented on Dec 23, 2024

    @swallowstalker
    swallowstalker
    on Dec 23, 2024
    Contributor

    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
  11. alfonsodev
    added a commit that references this issue on Oct 13, 2025

    feat(postgresql): Support system columns on tables (sqlc-dev#2871)

    5339bc9
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    📚 postgresql🔧 golanganalyzerbugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions