Pages

31 July, 2024

Postgres CTE value not being used in where clause

I'm trying to run this query that updates an ordering column after inserting the rows, and everything seems to work fine except using the id FROM the CTE query in the WHERE clause doesn't work, even though it works everywhere else. This is the query
WITH variant AS (
INSERT INTO accessory_variants (
accessory_id,
label,
multiple
) VALUES (
1,
'Colors',
FALSE
) RETURNING *
),
opts AS (
INSERT INTO accessory_variant_options (
accessory_id,
accessory_variant_id,
price,
label,
description
) VALUES (
1,
(SELECT id FROM variant),
100,
'Red',
'A red one'
),
(
1,
(SELECT id FROM variant),
100,
'Blue',
'A blue one'
) RETURNING id
),
ids AS (
SELECT JSONB_AGG(id) AS option_ids FROM opts
)
UPDATE
accessory_variants
SET
ordering = ids.option_ids
FROM
variant,
ids
WHERE
accessory_variants.id = variant.id



Where it gets weird is if I change it to something like this
...
UPDATE
accessory_variants
SET
ordering = TO_JSONB(variant.id)
FROM
variant,
ids
WHERE
accessory_variants.id = {existing row id}



The ordering table is populated with the correct id, so variant.id contains the correct id, but still can't be found by the where clause

No comments:

Post a Comment

Thanks