SQL - Using WITH to declare variable on INSERT INTO

Question

I'm trying to use WITH to declare a variable for a query when doing an INSERT INTO. I'm following https://stackoverflow.com/a/16552441/2923526 which gives the following example for a SELECT query:

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

I tried the following with no luck:

playground> CREATE TABLE foo (id numeric)
CREATE TABLE

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (x)
column "x" does not exist
LINE 1: WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (x)
                                                                ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x)
missing FROM-clause entry for table "consts"
LINE 1: ...consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x)
                                                              ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x) FROM consts
syntax error at or near "FROM"
LINE 1: ...AS (VALUES (2)) INSERT INTO foo VALUES (consts.x) FROM const...
                                                             ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo FROM consts VALUES (consts.x)
syntax error at or near "FROM"
LINE 1: WITH consts (x) AS (VALUES (2)) INSERT INTO foo FROM consts ...
                                                        ^

Note I am begginer to SQL so I'm looking to avoid solutions that imply using PLPGSQL

GMB · Accepted Answer · 2020-03-19 16:35:19Z

2

I think that you want:

WITH consts (x) AS (VALUES (2)) INSERT INTO foo SELECT x FROM consts

That is: the WITH clause creates a derived table, that you can then use within the main query; so you actually need to SELECT ... FROM the common table expression.

answered Mar 19, 2020 at 16:35

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

ffigari Over a year ago

That works, thank you. I'm confused about why the second VALUES inside the INSERT INTO is not needed, but guessing that's independent from the WITH statement (just syntax about INSERT INTO) right?

Belayer Over a year ago

You are correct. See Postgres Insert. Select your specific version if necessary. Stripped down to the basics the are 2 insert formats: Format 1. Insert (...) Values (...). Format 2. Insert(...) Select ... from table_name As @FranciscoFigari indicated the CTE (with) just supplied the table_name for Format 2.

Collectives™ on Stack Overflow

SQL - Using WITH to declare variable on INSERT INTO

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related