virtual generated column support

[Ghibranalj]

Version

1.30.0

What happened?

According to PostgreSQL's documentation, virtual generated columns are now supported.

However, when using a virtual generated column in an sqlc migration, it results in a syntax error.

Example migration:

CREATE TABLE IF NOT EXISTS customers(
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54),

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Running sqlc generate produces:

gibi@CreeprAir[chatbotv2 (master *%=) ]$ sqlc generate
# package sqlc
storage/migrations/000001_initial_schema.up.sql:22:5: syntax error at or near ","

If I explicitly use STORED, it works:

CREATE TABLE IF NOT EXISTS customers(
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54),

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

However, using VIRTUAL results in:

CREATE TABLE IF NOT EXISTS customers(
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) VIRTUAL,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Error:

gibi@CreeprAir[chatbotv2 (master *%=) ]$ sqlc generate
# package sqlc
storage/migrations/000001_initial_schema.up.sql:20:63: syntax error at or near "VIRTUAL"

I found this issue (#669) where it was mentioned that sqlc relies on pganalyze/pg_query_go for parsing. I am not familiar with that project and would appreciate clarification on whether this is a limitation in sqlc or needs to be addressed upstream in pg_query_go.

Relevant log output

Virtual:
gibi@CreeprAir[chatbotv2 (master *%=) ]$ sqlc generate
# package sqlc
storage/migrations/000001_initial_schema.up.sql:20:63: syntax error at or near "VIRTUAL"

without specifier:
gibi@CreeprAir[chatbotv2 (master *%=) ]$ sqlc generate
# package sqlc
storage/migrations/000001_initial_schema.up.sql:22:5: syntax error at or near ","

Database schema

CREATE TABLE IF NOT EXISTS customers(
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    phone STRING NOT NULL PRIMARY KEY,
    name STRING NOT NULL DEFAULT '',
    user_id UUID NOT NULL,
    whatsapp_number_id UUID NOT NULL,
    last_message TIMESTAMPTZ NOT NULL DEFAULT now(),
    taken_over_until TIMESTAMPTZ NOT NULL DEFAULT now(),
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54),

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

SQL queries

none

Configuration

version: "2"
sql:
  - engine: "postgresql"
    schema: "./storage/migrations"
    queries: "./storage/queries/*"
    gen:
      go:
        emit_json_tags: true
        package: "sqlc"
        out: "storage/sqlc"
        sql_package: "pgx/v5"
        overrides:
          - db_type: "timestamptz"
            nullable: false
            go_type:
              import: "time"
              type: "Time"
          - db_type: "uuid"
            go_type:
              import: "github.com/google/uuid"
              type: "UUID"
          - db_type: "uuid"
            nullable: true
            go_type:
              import: "github.com/google/uuid"
              type: "NullUUID"
          - db_type: "text"
            nullable: true
            go_type:
              import: "github.com/guregu/null/v6"
              type: "String"
          - db_type: "string"
            nullable: true
            go_type:
              import: "github.com/guregu/null/v6"
              type: "String"
          - db_type: "pg_catalog.int4"
            nullable: true
            go_type:
              import: "github.com/guregu/null/v6"
              type: "Int"
          - db_type: "pg_catalog.int4"
            nullable: false
            go_type: "int32"
          - db_type: "timestamptz"
            nullable: true
            go_type:
              import: "github.com/guregu/null/v6"
              type: "Time"

Playground URL

https://play.sqlc.dev/p/85524ee97d9dcf19e5605f2d7092ac7f8c65a5a81df55346130b2c8b882ae6e2

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

This is currently blocked on pganalyze/libpg_query#304