如何插入包含外键的行？

CREATE TABLE foo
(
    id BIGSERIAL     NOT NULL UNIQUE PRIMARY KEY,
    type VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE bar
(
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    description VARCHAR(40) NOT NULL UNIQUE,
    foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT
);

INSERT INTO foo (type) VALUES
    ( 'red' ),
    ( 'green' ),
    ( 'blue' );

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     SELECT id from foo WHERE type='blue' ),
    ( 'another row', SELECT id from foo WHERE type='red'  );

#1 楼

您的语法几乎是不错的，需要在子查询周围加上一些括号，然后它才能起作用：如果要插入的值很多，则使用较短的语法：

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     (SELECT id from foo WHERE type='blue') ),
    ( 'another row', (SELECT id from foo WHERE type='red' ) );

#2 楼

普通INSERT

INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM  (
   VALUES
      (text 'testing', text 'blue')  -- explicit type declaration; see below
    , ('another row' , 'red' )
    , ('new row1'    , 'purple')      -- purple does not exist in foo, yet
    , ('new row2'    , 'purple')
   ) val (description, type)
LEFT   JOIN foo f USING (type);

LEFT [OUTER] JOIN而不是[INNER] JOIN意味着即使在val中找不到匹配项，也将保留foo中的行。而是为NULL输入foo_id（如果定义了列NOT NULL，则会引发异常）。


子查询中的VALUES表达式与@ypercube的CTE相同。公用表表达式提供了附加功能，在大查询中更易于阅读，但它们也构成了优化的障碍（直到Postgres 12）。当不需要上述任何一项时，子查询通常会更快一些。


您可能需要显式类型转换。由于VALUES表达式没有直接附加到表上（例如INSERT ... VALUES ...中的表），因此不能派生类型，除非使用明确的类型，否则将使用默认数据类型。这可能并非在所有情况下都有效。在第一行中就足够了，其余的都在一行中。在单个SQL语句中，即时运行CTE是有帮助的：

 INSERT 

用于Postgres 9.6的旧sqlfiddle-在9.1。另请参阅下面的新提琴！
请注意要插入的另外两行。两者均为紫色，但在foo中尚不存在。第一行WITH sel AS ( SELECT val.description, val.type, f.id AS foo_id FROM ( VALUES (text 'testing', text 'blue') , ('another row', 'red' ) , ('new row1' , 'purple') , ('new row2' , 'purple') ) val (description, type) LEFT JOIN foo f USING (type) ) , ins AS ( INSERT INTO foo (type) SELECT DISTINCT type FROM sel WHERE foo_id IS NULL RETURNING id AS foo_id, type ) INSERT INTO bar (description, foo_id) SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id) FROM sel LEFT JOIN ins USING (type); 语句中有两行说明了对foo的需求。
分步说明


第一个CTE DISTINCT提供了多行输入数据。带有INSERT表达式的子查询sel可以替换为表或子查询作为源。立即在val到VALUES之间添加LEFT JOIN，以用于预先存在的foo行。其他所有行都以这种方式得到foo_id。


第二个CTE type将不同的新类型（foo_id IS NULL）插入ins中，并返回新生成的foo_id IS NULL-与foo一起返回插入行。


最后一个外部foo_id现在可以为每行插入一个type：是预先存在的类型，还是在步骤2中插入的。


严格来说，两个插入都是“并行”进行的，但是由于这是一个单条语句，默认INSERT约束不会抱怨。默认情况下，引用完整性在语句的末尾强制执行。
如果同时运行多个这些查询，则竞争情况很小。请参阅：

原子更新.. Postgres中的SELECT
SELECT或INSERT函数是否易于出现竞争条件？
如何为事务实现insert-if-not-found在可序列化的隔离级别？

真的只有在繁重的并发负载下才会发生（如果有的话）。与另一个答案中所宣传的缓存解决方案相比，机会很小。
重复使用的函数
创建一个将复合类型数组作为参数的SQL函数，并使用foo_id代替FOREIGN KEY表达式。或者，如果这样的数组的语法看起来过于混乱，请使用逗号分隔的字符串作为参数unnest(param)。例如形式：

'description1,type1;description2,type2;description3,type3'

，然后用它替换上面语句中的VALUES表达式：

SELECT split_part(x, ',', 1) AS description
       split_part(x, ',', 2) AS type
FROM   unnest(string_to_array(_param, ';')) x;

在Postgres 9.5或更高版本中具有UPSERT的函数
创建一个用于参数传递的自定义行类型。我们可以不用它，但它更简单：

CREATE TYPE foobar AS (description text, type text);

功能：

 _param 

调用：

 VALUES 

在这里拨弄
快速而坚如磐石，适用于具有并行事务的环境。
在除了上面的查询外，此功能...


...在CREATE OR REPLACE FUNCTION f_insert_foobar(VARIADIC _val foobar[]) RETURNS void LANGUAGE sql AS $func$ WITH val AS (SELECT * FROM unnest(_val)) -- well-known row type , typ AS ( SELECT v.type, f.id -- id NOT NULL where type already exists FROM (SELECT DISTINCT type FROM val) v -- DISTINCT! LEFT JOIN foo f USING (type) -- assuming no concurrent update/delete on foo -- else you might lock rows here. ) , ins AS ( INSERT INTO foo AS f (type) SELECT type FROM typ WHERE id IS NULL ON CONFLICT (type) DO UPDATE -- RARE cases of concurrent inserts SET type = EXCLUDED.type -- overwrite to make visible RETURNING f.type, f.id ) INSERT INTO bar AS b (description, foo_id) SELECT v.description, COALESCE(t.id, i.id) -- assuming most types pre-exist FROM val v LEFT JOIN typ t USING (type) -- already existed LEFT JOIN ins i USING (type) -- newly inserted ON CONFLICT (description) DO UPDATE -- description already exists SET foo_id = EXCLUDED.foo_id -- real UPSERT this time WHERE b.foo_id <> EXCLUDED.foo_id; -- only if actually changed $func$; 上应用SELECT f_insert_foobar( '(testing,blue)' , '(another row,red)' , '(new row1,purple)' , '(new row2,green)' , '("with,comma",green)' -- added to demonstrate row syntax ); 或SELECT：插入FK表中尚不存在的任何INSERT。假设大多数类型已经存在。


...在foo上应用type或INSERT（真正的“ UPSERT”）：如果UPDATE已经存在，则更新其bar-但前提是：实际上发生了变化。请参阅：

如何（或可以）在多列上选择DISTINCT？



...将值作为众所周知的行传递具有description函数参数的类型。注意默认的最大100个功能参数！请参见：

选择行以使名称与pgsql函数的输入数组的元素匹配

还有许多其他方法可以传递多行...


相关：

如何在PostgreSQL中将RETURNING和ON CONFLICT一起使用？
如何在从INSERT ... RE ONING中包括被排除的行... ON CONFLICT

>

#3 楼

抬头。您基本上需要foo id才能将它们插入bar。

不是postgres专用的，顺便说一句。 （并且您没有这样标记）-通常这就是SQL的工作方式。这里没有捷径。

但是，在应用程序方面，您可能会在内存中缓存foo项。我的表通常具有多达3个唯一字段：


Id（整数或其他内容）是表级主键。
标识符，它是用作以下项的GUID稳定的ID应用程序级别（并且可能在URL等中暴露给客户） 。那是一个客户集标识符。

示例：


（在交易应用程序中）帐户
-> ID是用于外键的int 。
->标识符是Guid，在Web门户等中使用-始终被接受。
->手动设置代码。规则：一旦设置，它就不会更改。

很明显，当您要将某些内容链接到帐户时-从技术上讲，您首先必须获得ID-但鉴于标识符和代码都永远不会更改，内存中的正缓存会阻止大多数查询访问数据库。