主页 / dba / 问题 / 46410
Accepted
Stéphane
Asked: 2013-07-16 20:07:10 +0800 CST

如何插入包含外键的行？

  • 772

使用 PostgreSQL v9.1。我有以下表格：

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
);

假设第一个表foo是这样填充的：

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

bar有没有办法通过引用表轻松插入行foo？还是我必须分两步完成，首先查找foo我想要的类型，然后在 中插入一个新行bar？

这是一个伪代码示例，显示了我希望可以完成的操作：

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

3 个回答

  1. Best Answer

    您的语法几乎很好，需要在子查询周围加上一些括号，它会起作用：

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

    在DB-Fiddle测试

    另一种方法，如果您要插入很多值，则使用更短的语法：

    WITH ins (description, type) AS
( VALUES
    ( 'more testing',   'blue') ,
    ( 'yet another row', 'green' )
)  
INSERT INTO bar
   (description, foo_id) 
SELECT 
    ins.description, foo.id
FROM 
  foo JOIN ins
    ON ins.type = foo.type ;
    • 106

  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输入 for foo_id（如果定义了列，则会引发异常NOT NULL）。

    • 子查询中的VALUES表达式与@ypercube 的CTE 相同。公用表表达式提供了额外的功能，并且在大型查询中更易于阅读，但它们也构成了优化障碍（直到 Postgres 12）。当以上都不需要时，子查询通常会快一点。

    • 您可能需要显式类型转换。由于VALUES表达式不直接附加到表（如 in INSERT ... VALUES ...），因此无法派生类型并且使用默认数据类型，除非显式键入。这可能不适用于所有情况。在第一行就足够了，其余的都在排队。

    INSERT同时缺少 FK 行

    foo要在单个 SQL 语句中动态创建缺失的条目，CTE 很有帮助：

    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);

    Postgres 9.6 的旧sqlfiddle - 在 9.1 中的工作方式相同。另请参阅下面的新小提琴！

    注意要插入的另外两行。两者都是紫色的，目前尚不存在foo。两行来说明DISTINCT第一个INSERT语句中的需要。

    分步说明

    1. 第一个 CTEsel提供多行输入数据。val可以将带有表达式的子查询VALUES替换为表或子查询作为源。立即追加预先存在的LEFT JOIN行。所有其他行都采用这种方式。foofoo_idtypefoo_id IS NULL

    2. 第二个 CTEins将不同的新类型 ( foo_id IS NULL) 插入到foo中，并返回新生成的foo_id- 以及type要加入的插入行。

    3. 最终的外部INSERT现在可以为每一行插入一个foo_id：要么是预先存在的类型，要么是在步骤 2 中插入的。

    严格来说，两个插入都是“并行”发生的，但由于这是一条语句，默认FOREIGN KEY约束不会抱怨。默认情况下，引用完整性在语句的末尾强制执行。

    如果您同时运行多个这样的查询，则会出现很小的竞争条件。看：

    如果有的话，真的只发生在高并发负载下。与另一个答案中宣传的缓存解决方案相比，机会非常小。

    重复使用功能

    创建一个 SQL 函数，该函数将复合类型的数组作为参数并用于unnest(param)代替VALUES表达式。

    或者，如果此类数组的语法看起来过于混乱，请使用逗号分隔的字符串作为参数_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);

    功能：

    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
   );

    db<>在这里摆弄

    对于具有并发事务的环境，快速且坚如磐石。

    除了上面的查询，这个函数...

    有关的：

    • 54

  3. 抬头。您基本上需要 foo id 将它们插入到 bar 中。

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

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

    • 作为表级主键的 ID（整数或其他值）。
    • 标识符，这是一个 GUID，用作稳定的 ID 应用程序级别（并且可能在 URL 等中向客户公开）
    • 代码 - 可能存在的字符串，如果存在则必须是唯一的（sql server：过滤的唯一索引不为空）。那是一个客户集标识符。

    例子：

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

    显然，当您想将某些内容链接到帐户时 - 从技术上讲，您首先必须获取 Id - 但鉴于标识符和代码一旦存在就永远不会改变，内存中的正缓存可以阻止大多数查找访问数据库。

    • 6

相关问题