PostgreSQL: Sub-select inside insert

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_license`s 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?

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'

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

**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**".