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?

Answer 1

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'