在不存在的地方插入多行PostgresQL [英] Insert multiple rows where not exists PostgresQL

问题描述

我想生成一个sql查询，以批量插入表中不存在的一系列行。我当前的设置对每个记录插入都进行新查询，类似于 PostgreSQL中的哪里不存在会给出语法错误，但是我想将其移至单个查询以优化性能，因为我当前的设置可以一次生成数百个查询。现在，我正在尝试类似下面添加的示例：

I'd like to generate a single sql query to mass-insert a series of rows that don't exist on a table. My current setup makes a new query for each record insertion similar to the solution detailed in WHERE NOT EXISTS in PostgreSQL gives syntax error, but I'd like to move this to a single query to optimize performance since my current setup could generate several hundred queries at a time. Right now I'm trying something like the example I've added below:

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返回以下错误：

Postgres returns the following error:

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

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

The problem is that PostgresQL doesn't seem to want to take a series of values when using SELECT. Conversely, I can make the insertions using VALUES, but I can't then prevent duplicates from being generated using WHERE NOT EXISTS.

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

http://www.techonthenet.com/postgresql/insert.php suggests in the section EXAMPLE - USING SUB-SELECT that multiple records should be insertable from another referenced table using SELECT, so I'm wondering why I can't seem to pass in a series of values to insert. The values I'm passing are coming from an external API, so I need to generate the values to insert by hand.

推荐答案

您的 select 并没有按照您的想象做。

Your select is not doing what you think it does.

PostgreSQL中最紧凑的版本是这样的：

The most compact version in PostgreSQL would be something like this:

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

这篇关于在不存在的地方插入多行PostgresQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！