PostgreSQL won't accept INSERT with VALUES and FROM?

Question

I am trying to obtain a value obtained from a SELECT, and then INSERT that value, together with other values, into another table:

WITH data AS (SELECT name FROM programmes WHERE id = $1)
INSERT INTO purchases (name, other_details, some_more_stuff)
VALUES (data.name, $2, $3) FROM data;

But PostgreSQL gives 42601 ERROR: syntax error at or near "FROM" LINE 1: ...(data.name, $2, $3) FROM data.

INSERT's documentation doesn't give any example of VALUES and FROM together in the same query. What is the correct syntax for this kind of query? Or is it not possible to phrase my query in this manner?

klin · Accepted Answer · 2017-03-25T19:39:00.837

You don't need common table expression.

INSERT INTO purchases (name, other_details, some_more_stuff) 
SELECT name, $2, $3
FROM programmes 
WHERE id = $1

The syntax you have tried to apply is incorrect. You cannot use columns from a table or query in the VALUES list, because the list must contain only constants. From the documentation (emphasis added):

VALUES provides a way to generate a "constant table" that can be used in a query without having to actually create and populate a table on-disk.

See also VALUES syntax.

score 3 · Answer 2 · answered Mar 25 '17 at 16:03

Use insert . . . select:

WITH data AS (
      SELECT name
      FROM programmes 
      WHERE id = $1
    )
INSERT INTO purchases (name, other_details, some_more_stuff) 
    SELECT data.name, $2, $3
    FROM data;

It is not just Postgres. This is how SQL works. To be honest, I don't think learning insert . . . values is particularly useful, because you can do everything and more using insert . . . select. (Well, unless you are using MS Access which doesn't allow "constant" subqueries.)

PostgreSQL won't accept INSERT with VALUES and FROM?

2 Answers2