COALESCE doesn't appear to work within a CTE
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
Facing the same issue. did you find a solution/workaround?
Can this be reproduced in the playground: https://play.sqlc.dev/ ?
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