CODEWITHSUNDEEP
sqlpostgresqlsql-insertcommon-table-expression
ffigari
ffigari

Reputation: 451

SQL - Using WITH to declare variable on INSERT INTO

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

Upvotes: 1

Views: 1407

Answers (1)

GMB
GMB

Reputation: 222492

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.

Upvotes: 2

Related Questions