René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Column names in ON CONFLICT DO UPDATE SET clause not validated","articleBody":"### Version\n\n1.30.0\n\n### What happened?\n\n # Bug: sqlc does not validate column names in ON CONFLICT DO UPDATE SET clause\n\n  ## Description\n\n  sqlc fails to validate column names in `ON CONFLICT ... DO UPDATE SET` clauses against the table schema. This allows invalid queries to be generated that will only fail at runtime when executed against the database.\n\n  ## Steps to Reproduce\n\n  1. Create a schema file (`migrations/schema.sql`):\n  ```sql\n  CREATE TABLE IF NOT EXISTS cart_items\n  (\n      owner_id       VARCHAR(255)                        NOT NULL,\n      product_id     UUID                                NOT NULL,\n      price_amount   DECIMAL                             NOT NULL,\n      price_currency VARCHAR(3)                          NOT NULL,\n      created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,\n      PRIMARY KEY (owner_id, product_id)\n  );\n\n  CREATE INDEX idx_cart_items_owner ON cart_items (owner_id);\n\n  2. Create a query file (query.sql) with an invalid column name in the SET clause:\n  -- name: AddItem :exec\n  INSERT INTO cart_items (owner_id, product_id, price_amount, price_currency)\n  VALUES ($1, $2, $3, $4)\n  ON CONFLICT (owner_id, product_id) DO UPDATE\n      SET price_amount1 = EXCLUDED.price_amount1, price_currency = EXCLUDED.price_currency;\n\n  Note: The column is price_amount (exists), but the query references price_amount1 (does not exist)\n\n  3. Create sqlc.yaml:\n  version: \"2\"\n  sql:\n    - engine: \"postgresql\"\n      schema: \"migrations/schema.sql\"\n      queries: \"query.sql\"\n      gen:\n        go:\n          package: \"querytest\"\n          out: \"generated\"\n\n  4. Run sqlc generate\n\n  Expected Behavior\n\n  sqlc should report an error similar to:\n  query.sql:5:15: column \"price_amount1\" of relation \"cart_items\" does not exist\n\n  This matches the behavior when running the same query through PostgreSQL directly.\n\n  Actual Behavior\n\n  sqlc generates code successfully without any errors, embedding the invalid SQL:\n  const addItem = `... SET price_amount1 = EXCLUDED.price_amount1 ...`\n\n  The generated Go code compiles successfully, but the query will fail at runtime with a database error.\n\n  Root Cause\n\n  sqlc does not validate column names in ON CONFLICT ... DO UPDATE SET clauses against the table schema. The validation code path is missing:\n\n  - OnConflictClause is parsed correctly by the PostgreSQL engine\n  - But it's never validated in the compiler phase\n  - Column validation happens in internal/compiler/output_columns.go for SELECT/WHERE clauses, but not for ON CONFLICT clauses\n  - The validate.InsertStmt() function in internal/sql/validate/insert_stmt.go only validates column count vs value count, not SET clause columns\n\n  Files Affected\n\n  - internal/compiler/analyze.go - No validation for ON CONFLICT\n  - internal/sql/validate/insert_stmt.go - Missing ON CONFLICT SET column validation\n  - internal/compiler/output_columns.go - Missing ON CONFLICT SET clause handling\n\n  Workaround\n\n  Until this is fixed, ensure column names in ON CONFLICT DO UPDATE SET clauses are manually verified against the schema.\n\n  Environment\n\n  - sqlc version: v1.30.0 (confirmed on latest main)\n  - Database engine: PostgreSQL\n  - Go version: 1.25.0+\n\n### Relevant log output\n\n```shell\n\n```\n\n### Database schema\n\n```sql\n\n```\n\n### SQL queries\n\n```sql\n\n```\n\n### Configuration\n\n```yaml\n\n```\n\n### Playground URL\n\n_No response_\n\n### What operating system are you using?\n\nmacOS\n\n### What database engines are you using?\n\nPostgreSQL\n\n### What type of code are you generating?\n\nGo","author":{"url":"https://github.com/nikolayk812","@type":"Person","name":"nikolayk812"},"datePublished":"2026-02-08T15:41:39.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/4291/sqlc/issues/4291"}