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' 

Answer 2

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