sql - 在PostgreSQL中从SELECT插入多行

如PostgreSQL manual中所述，可以在一个INSERT语句中添加多行:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('6120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

像这样的嵌套SELECT查询也可以工作:

INSERT INTO films (code, title, did, date_prod, kind)
     SELECT table.code, 'Tampopo', 110, '1985-02-10', 'Comedy'
     FROM other_table AS table2;

但是，我似乎无法想出一种同时做这两种方法的方法:

INSERT INTO films (code, title, did, date_prod, kind)
SELECT
    (table.code + 100, 'abc', NULL, t2.date_prod, t2.kind),
    (table.code + 200, 'xyz', NULL, t2.date_prod, t2.kind)
FROM other_table AS t2;

如果other_table仅包含(61717 | 'Tampopo' | NULL | '1985-02-10' | 'Comedy')，则结果表将如下所示:

 code | title |  did |   date_prod  |   kind
--------------------------------------------------
61817 | 'abc' | NULL | '1985-02-10' | 'Comedy'
61917 | 'xyz' | NULL | '1985-02-10' | 'Comedy'

This fiddle应该进一步说明...

您能指出我要去哪里了吗？如果可能的话，我想避免在other_table AS t2中进行多次查找，因为它是VIEW，并且要花费大量的时间来构造。

最佳答案

通常，您可以将select与union结合使用:

insert into films (code, title, did, date_prod, kind)
    select t2.code + 100, t2.title, NULL::float, t2.date_prod, t2.kind
    from other_table AS t2
union
    select t2.code + 200, t2.title, NULL::float, t2.date_prod, t2.kind
    from other_table AS t2;

在您描述的特定情况下，可以使用unnest (array []):

insert into films (code, title, did, date_prod, kind)
    select
        unnest(array[t2.code + 100, t2.code + 200]),
        t2.title, NULL::float, t2.date_prod, t2.kind
    from other_table AS t2

关于sql - 在PostgreSQL中从SELECT插入多行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33201243/