You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Insert statement with WITH clause used as SELECT field #2886
I've tried 2 variations of the same query using WITH (v AS ...) and referencing that v as one of the SELECTed fields. While both variations produce the expected results using psql, they fail with different errors when compiling them using sqlc.
Using `INSERT INTO ... WITH ... SELECT ...
-- name: GenerateNewHistoricalData :execINSERT INTO historical_data(id, default_value, version)
WITH v AS (SELECT nextval('historical_data_version_seq'))
SELECT id, default_value, (table v)
FROM data;
Error: query.sql:18:1: relation "v" does not exist
Using WITH ... INSERT INTO ... SELECT ...:
-- name: GenerateNewHistoricalDataTakeTwo :exec
WITH v AS (SELECT nextval('historical_data_version_seq'))
INSERT INTO historical_data(id, default_value, version)
SELECT id, default_value, (table v)
FROM data;
Error: query.sql:24:1: edit start location is out of bounds
Relevant log output
No response
Database schema
CREATETABLEdata (
id TEXTNOT NULL,
value TEXTNOT NULL,
CONSTRAINT data_pkey PRIMARY KEY(id)
);
CREATETABLEhistorical_data (
id TEXTNOT NULL,
value TEXTNOT NULL,
version BIGINTNOT NULL,
CONSTRAINT historical_data_pkey PRIMARY KEY(id, version)
);
CREATESEQUENCEhistorical_data_version_seq;
The first error will be fixed by #2805. You can fix the second error by explicitly selecting the column in the subselect.
-- name: GenerateNewHistoricalDataTakeTwo :exec
WITH v AS (SELECT nextval('historical_data_version_seq') as nv)
INSERT INTO historical_data(id, value, version)
SELECT id, value, (select nv from v)
FROM data;
Activity
kyleconroy commentedon Oct 19, 2023
The first error will be fixed by #2805. You can fix the second error by explicitly selecting the column in the subselect.
https://play.sqlc.dev/p/af2fda7ab8fcff8040cf19be8dd85ce78fec9a51fd4fdbab187ffa6b682939b1
kampde commentedon Oct 20, 2023
Thanks, that works wonderfully :D
If you prefer you can close the issue now or maybe you want to keep it opened for tracking or whatever.