1

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
  • 216,147
  • 25
  • 84
  • 135
ffigari
  • 431
  • 6
  • 18

1 Answers1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 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? – ffigari Mar 19 '20 at 16:42
  • 1
    You are correct. See [Postgres Insert](https://www.postgresql.org/docs/10/sql-insert.html). 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. – Belayer Mar 20 '20 at 00:01