使用unnest函数插入-跳过序列列中的数字 [英] insert with unnest function - skips a number in serial column

问题描述

我正在尝试在插入中使用嵌套"功能，
这样做时，序列号会为每个插入内容跳过一个数字，
请帮助我解决这个问题...

i am trying to use "unnest" function in insert,
while doing so, the serial skips a number for each insert,
plz help me to solve this...

mydb=# \d tab1  
                         Table "public.tab1"  
 Column |  Type   |                     Modifiers                         
--------+---------+---------------------------------------------------  
 id     | integer | not null default nextval('tab1_id_seq'::regclass)  
 col1   | integer |   
 col2   | integer |   
Indexes:  
    "tab1_pkey" PRIMARY KEY, btree (id)  

mydb=# insert into tab1(id,col1,col2) values (nextval('tab1_id_seq'),1,unnest(array[4,5]));  
INSERT 0 2  
mydb=# select * from tab1;  
 id | col1 | col2   
----+------+------  
  1 |    1 |    4  
  2 |    1 |    5  
(2 rows)  

mydb=# insert into tab1(id,col1,col2) values (nextval('tab1_id_seq'),2,unnest(array[4,5]));  
INSERT 0 2  
mydb=# select * from tab1;  
 id | col1 | col2   
----+------+------  
  1 |    1 |    4  
  2 |    1 |    5  
  4 |    2 |    4  
  5 |    2 |    5  
(4 rows)  

mydb=# insert into tab1(col1,col2) values(2,unnest(array[4,5]));  
INSERT 0 2  
mydb=# select * from tab1;  
 id | col1 | col2   
----+------+------  
  1 |    1 |    4  
  2 |    1 |    5  
  4 |    2 |    4  
  5 |    2 |    5  
  7 |    2 |    4  
  8 |    2 |    5  
(6 rows)  

mydb=# insert into tab1(col2) values(unnest(array[4,5]));  
INSERT 0 2  
mydb=# select * from tab1;  
 id | col1 | col2   
----+------+------  
  1 |    1 |    4  
  2 |    1 |    5  
  4 |    2 |    4  
  5 |    2 |    5  
  7 |    2 |    4  
  8 |    2 |    5  
 10 |      |    4  
 11 |      |    5  
(8 rows)  

mydb=# select nextval('tab1_id_seq');  
 nextval   
---------  
      13  
(1 row)  

对于每次插入，它都会跳过id列中的数字，请帮助我解决此问题...

推荐答案

unnest返回多行，因此在VALUES的单行中使用它有点麻烦.尽管它确实有效，但似乎对nextval调用进行了两次评估.

unnest returns multiple rows, so using it inside a single row of VALUES is a bit of a hack. Although it does work, it appears that the nextval call is somehow evaluated twice.

您可以将插入内容写为INSERT INTO ... SELECT ...而不是INSERT INTO ... VALUES:在PostgreSQL中，VALUES只是行构造函数.因此，请考虑编写如下内容:

You can write an insert as INSERT INTO ... SELECT ... rather than INSERT INTO ... VALUES: in PostgreSQL, VALUES is just a row constructor. So consider writing something like this:

insert into tab1(col1, col2) select 1, unnest(array[4,5])

这篇关于使用unnest函数插入-跳过序列列中的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！