PostgreSQL: Sub-select inside insert

Question

I have a table called map_tags:

map_id | map_license | map_desc

And another table (widgets) whose records contains a foreign key reference (1 to 1) to a map_tags record:

widget_id | map_id | widget_name

Given the constraint that all map_licenses are unique (however are not set up as keys on map_tags), then if I have a map_license and a widget_name, I'd like to perform an insert on widgets all inside of the same SQL statement:

INSERT INTO
    widgets w
(
    map_id,
    widget_name
)
VALUES (
    (
        SELECT
            mt.map_id
        FROM
            map_tags mt
        WHERE
            // This should work and return a single record because map_license is unique
            mt.map_license = '12345'
    ),
    'Bupo'
)

I believe I'm on the right track but know right off the bat that this is incorrect SQL for Postgres. Does anybody know the proper way to achieve such a single query?

Have never done it with PostgreSQL, but shouldn't it look like `INSERT INTO widgets SELECT NULL, map_id, 'Bupo' FROM map_tags WHERE map_license = '12345'`? — raina77ow, Oct 22 '12 at 17:50
Thanks @raina77ow (+1) - I'm not sure if that's what it should be. The `SELECT` statement in lieu of the `(...) VALUES(...)` syntax is definitely throwing me off though. Would you mind explaining to me how that should work? Thanks again! — Bantha Fodder, Oct 22 '12 at 17:53
Well, I just use it like [this](http://dev.mysql.com/doc/refman/5.1/en/insert-select.html) in MySQL... The reason, I suppose, is that you have to build the whole set of data that would be inserted - and VALUES ((SELECT smth), 'smth_else') just doesn't do it. ) — raina77ow, Oct 22 '12 at 17:55
You don't actually need to use values according to this syntax: INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1; — de.la.ru, Sep 21 '16 at 17:26

score 69 · Answer 1 · answered Oct 22 '12 at 17:50

69

Use the INSERT INTO SELECT variant, including whatever constants right into the SELECT statement.

The PostgreSQL INSERT syntax is:

INSERT INTO table [ ( column [, ...] ) ]
 { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
 [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Take note of the query option at the end of the second line above.

Here is an example for you.

INSERT INTO 
    widgets
    (
        map_id,
        widget_name
    )
SELECT 
   mt.map_id,
   'Bupo'
FROM
    map_tags mt
WHERE
    mt.map_license = '12345'

answered Oct 22 '12 at 17:50

gahooa

131,293
12
98
101

2

Why has the other almost identical but less complete and one minute later answer been voted up more and accepted? – Chris Peacock Apr 26 '17 at 11:43
Because stack exchange was becoming a much too powerful tool. So they decided to leave it pretty broken. – Henrik Sommerland Sep 08 '20 at 18:20

score 61 · Accepted Answer · answered Oct 22 '12 at 17:51

61

INSERT INTO widgets
(
    map_id,
    widget_name
)
SELECT
    mt.map_id, 'Bupo'
FROM
    map_tags mt
WHERE
    mt.map_license = '12345'

answered Oct 22 '12 at 17:51

Clodoaldo Neto

118,695
26
233
260

This is a super useful syntax when the table you want to insert into has a SERIAL PK or similar – dougajmcdonald Feb 20 '17 at 12:49

score 0 · Answer 3 · answered Jul 30 '18 at 00:17

Quick Answer: You don't have "a single record" you have a "set with 1 record" If this were javascript: You have an "array with 1 value" not "1 value".

In your example, one record may be returned in the sub-query, but you are still trying to unpack an "array" of records into separate actual parameters into a place that takes only 1 parameter.

It took me a few hours to wrap my head around the "why not". As I was trying to do something very similiar:

Here are my notes:

tb_table01: (no records)
+---+---+---+
| a | b | c | << column names
+---+---+---+

tb_table02:
+---+---+---+
| a | b | c | << column names
+---+---+---+
|'d'|'d'|'d'| << record #1
+---+---+---+
|'e'|'e'|'e'| << record #2
+---+---+---+
|'f'|'f'|'f'| << record #3
+---+---+---+

--This statement will fail:
INSERT into tb_table01
    ( a, b, c )
VALUES
    (  'record_1.a', 'record_1.b', 'record_1.c' ),
    (  'record_2.a', 'record_2.b', 'record_2.c' ),

    -- This sub query has multiple
    -- rows returned. And they are NOT
    -- automatically unpacked like in 
    -- javascript were you can send an
    -- array to a variadic function.
    (
        SELECT a,b,c from tb_table02
    ) 
    ;

Basically, don't think of "VALUES" as a variadic function that can unpack an array of records. There is no argument unpacking here like you would have in a javascript function. Such as:

function takeValues( ...values ){ 
    values.forEach((v)=>{ console.log( v ) });
};

var records = [ [1,2,3],[4,5,6],[7,8,9] ];
takeValues( records );

//:RESULT:
//: console.log #1 : [1,2,3]
//: console.log #2 : [4,5,7]
//: console.log #3 : [7,8,9]

Back to your SQL question:

The reality of this functionality not existing does not change just because your sub-selection contains only one result. It is a "set with one record" not "a single record".

PostgreSQL: Sub-select inside insert

3 Answers3