sqlc icon indicating copy to clipboard operation
sqlc copied to clipboard

COALESCE doesn't appear to work within a CTE

Open Bronya-Rand opened this issue 1 year ago • 3 comments

Version

1.26.0

What happened?

When creating WITH statements in SQL, I've noticed that if I was to use COALESCE within a WITH that updates data, that it would result in sqlc generate saying that the column does not exist even though it does exist within the table using psql.

Relevant log output

# package queries
sql/collections.sql:30:9: column "updated_at" does not exist

Database schema

id | user_id | name | description | created_at | updated_at

SQL queries

-- name: CollectionUpdate :one
WITH updated_collection AS (
    UPDATE user_collections
    SET
        collection_name = COALESCE(sqlc.narg(collection_name), collection_name),
        collection_description = COALESCE(sqlc.narg(collection_description), collection_description),
        is_public = COALESCE(sqlc.narg(is_public), is_public),
        updated_at = COALESCE(sqlc.narg(updated_at), CURRENT_TIMESTAMP)
    WHERE id = sqlc.arg(id)
    RETURNING id
), deleted_characters AS (
    DELETE FROM collection_characters
    WHERE collection_id = (SELECT id FROM updated_collection) AND character_id != ALL(sqlc.narg('character_ids')::uuid[])
    RETURNING *
)

INSERT INTO collection_characters (character_id, collection_id)
SELECT UNNEST(sqlc.narg('character_ids')::uuid[]), (SELECT id FROM updated_collection)
ON CONFLICT DO NOTHING
RETURNING *;

Configuration

version: "2"
sql:
  - schema: "./migrate/migrations/"
    queries: "./sql/"
    engine: "postgresql"
    gen:
      go:
        emit_interface: true
        package: "queries"
        out: "queries"
overrides:
  go: null
plugins: []
rules: []

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Bronya-Rand avatar May 14 '24 01:05 Bronya-Rand

Facing the same issue. did you find a solution/workaround?

Abdullah-AlAttar avatar Jun 14 '24 11:06 Abdullah-AlAttar

Can this be reproduced in the playground: https://play.sqlc.dev/ ?

orisano avatar Jun 30 '24 05:06 orisano

encountered this issue as well specifically when using UPDATE with COALESCE within CTE

without COALESCE this works https://play.sqlc.dev/p/910a8dd6ff4e9d5faf6eaf51778a28b4f72105a8f4a90773e7efbb0fde27f52e

but got the column doesn't exist with COALESCE https://play.sqlc.dev/p/0bb6d311cc9b0bd9303aa8195f75b3f0d30e4abf12ade28e3ff76c6ba2f16bcf

ghiffaryuthian avatar May 22 '25 15:05 ghiffaryuthian