SQL PostgreSQL：在插入中进行子选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13016851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Sub-select inside insert
提问by Bantha Fodder
I have a table called map_tags:
我有一张桌子叫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_tagsrecord:
另一个表 ( widgets) 的记录包含对记录的外键引用（1 到 1）map_tags：
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_licenseand a widget_name, I'd like to perform an insert on widgetsall inside of the same SQL statement:
考虑到所有map_licenses 都是唯一的约束（但是没有设置为 上的键map_tags），那么如果我有 amap_license和 a widget_name，我想widgets在同一个 SQL 语句的所有内部执行插入：
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 believeI'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?
我相信我在正确的轨道上，但马上就知道这对于 Postgres 来说是不正确的 SQL。有人知道实现这种单一查询的正确方法吗？
回答by gahooa
Use the INSERT INTO SELECTvariant, including whatever constants right into the SELECTstatement.
使用INSERT INTO SELECT变体，包括SELECT语句中的任何常量。
The PostgreSQL INSERTsyntax is:
PostgreSQLINSERT语法是：
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Take note of the queryoption 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'
回答by Clodoaldo Neto
INSERT INTO widgets
(
map_id,
widget_name
)
SELECT
mt.map_id, 'Bupo'
FROM
map_tags mt
WHERE
mt.map_license = '12345'
回答by J.M.I. MADISON
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".
快速回答：您没有“单个记录”，您有一个“设置 1 个记录”如果这是 javascript：您有一个“具有 1 个值的数组”而不是“1 个值”。
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.
在您的示例中，子查询中可能会返回一条记录，但您仍在尝试将记录的“数组”解包为单独的实际参数，并将其放入仅包含 1 个参数的位置。
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'tthink of "VALUES" as a variadicfunction that can unpack an array of records. There is no argument unpacking here like you would have in a javascript function. Such as:
基本上，不要将“ VALUES”视为 可以解压缩记录数组的可变参数函数。这里没有像在 javascript 函数中那样解包的参数。如：
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:
回到你的 SQL 问题：
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".
不存在此功能的现实不会因为您的子选择只包含一个结果而改变。它是一个“单条记录”，而不是“单条记录”。
