在PostgresQL不存在的地方插入多行

Question

我想生成一个sql查询来批量插入表中不存在的一系列行。我当前的设置为每个记录插入创建一个新的查询，类似于PostgreSQL中WHERE NOT EXISTS给出语法错误的解决方案，但我想将其移动到单个查询以优化性能，因为我当前的设置一次可以生成数百个查询。现在我正在尝试类似于我在下面添加的示例:

INSERT INTO users (first_name, last_name, uid) 
SELECT ( 'John', 'Doe', '3sldkjfksjd'), ( 'Jane', 'Doe', 'adslkejkdsjfds')
WHERE NOT EXISTS (
  SELECT * FROM users WHERE uid IN ('3sldkjfksjd', 'adslkejkdsjfds')
)

Postgres返回以下错误:

PG::Error: ERROR:  INSERT has more target columns than expressions

问题是PostgresQL在使用SELECT时似乎不希望获取一系列值。相反，我可以使用VALUES进行插入，但我不能使用WHERE NOT EXISTS来防止生成重复项。

http://www.techonthenet.com/postgresql/insert.php建议在部分示例-使用子选择，多个记录应该从另一个引用表使用SELECT插入，所以我想知道为什么我似乎不能传递一系列值插入。我传递的值来自外部API，所以我需要手工生成要插入的值。

Answer 1

您的select没有做您认为它做的事情。

PostgreSQL中最简洁的版本应该是这样的:

with data(first_name, last_name, uid)  as (
   values
      ( 'John', 'Doe', '3sldkjfksjd'),
      ( 'Jane', 'Doe', 'adslkejkdsjfds')
) 
insert into users (first_name, last_name, uid) 
select d.first_name, d.last_name, d.uid
from data d
where not exists (select 1
                  from users u2
                  where u2.uid = d.uid);

大致相当于:

insert into users (first_name, last_name, uid) 
select d.first_name, d.last_name, d.uid
from (
   select 'John' as first_name, 'Doe' as last_name, '3sldkjfksjd' as uid
   union all
   select 'Jane', 'Doe', 'adslkejkdsjfds'
) as d
where not exists (select 1
                  from users u2
                  where u2.uid = d.uid);

Answer 2

a_horse_with_no_name的答案实际上有一个语法错误，缺少最后的右父括号，但除此之外是正确的方法。

更新:对于像我这样遇到这种情况的人来说，如果您有需要进行类型转换的列(例如PG 9.5中的时间戳或uid或jsonb)，您必须在传递给查询的值中声明:

-- insert multiple if not exists
-- where another_column_name is of type uuid, with strings cast as uuids
-- where created_at and updated_at is of type timestamp, with strings cast as timestamps
WITH data (id, some_column_name, another_column_name, created_at, updated_at) AS (
  VALUES
    (<id value>, <some_column_name_value>, 'a5fa7660-8273-4ffd-b832-d94f081a4661'::uuid, '2016-06-13T12:15:27.552-07:00'::timestamp, '2016-06-13T12:15:27.879-07:00'::timestamp),
    (<id value>, <some_column_name_value>, 'b9b17117-1e90-45c5-8f62-d03412d407dd'::uuid, '2016-06-13T12:08:17.683-07:00'::timestamp, '2016-06-13T12:08:17.801-07:00'::timestamp)
)
INSERT INTO table_name (id, some_column_name, another_column_name, created_at, updated_at)
SELECT d.id, d.survey_id, d.arrival_uuid, d.gf_created_at, d.gf_updated_at
FROM data d
WHERE NOT EXISTS (SELECT 1 FROM table_name t WHERE t.id = d.id);

a_horse_with_no_name的答案今天拯救了我的一个项目，但必须做这些调整，使它完美