Why is this syntax invalid in postgresql?

Question

I'm trying to insert some columns in a new table and set the ids in another one. I know this can be achieved using the WITH AS statement but I'm curious why it's not working this way

UPDATE "a" 
SET "c1" = cp.id 
FROM (
    INSERT INTO "b" ("c2", "c3", "c4") 
    SELECT "c2", "c3", "c4" 
    FROM "a" 
    RETURNING id
) AS cp;

This is the error I'm getting:

ERROR:  syntax error at or near "INTO"
LINE 4:  INSERT INTO "b" ("c2", "c3", "c4") 
                ^
SQL state: 42601
Character: 46

Generally a CTE is just syntactic sugar for a subquery. However, in a few cases like this one, you are allowed to do things in a CTE that are not allowed in a subquery. I would just chalk this up to: "It's just the way it is". — JNevill
– JNevill, Commented Jun 23, 2023 at 17:34
I never know with the downvotes, honestly. It's a clear question. It's well written. There isn't any great resource online that could have answered this for you as the answer is likely VERY DEEP postgres server implementation logic. People are weird. — JNevill
– JNevill, Commented Jun 23, 2023 at 19:11

cobp · Accepted Answer · 2023-06-23 17:34:26Z

0

As per the UPDATE syntax https://www.postgresql.org/docs/current/sql-update.html, from_item should be a table expression. But the "INSERT INTO" can not be a table expression that can be specified in FROM clause.

answered Jun 23, 2023 at 17:34

cobp

7821 gold badge6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is this syntax invalid in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related