SET IDENTITY_INSERT PostgreSQL [英] SET IDENTITY_INSERT postgresql

问题描述

我对postgresql脚本不熟悉。

I'm not familiar with postgresql script.

我已经在ms sql服务器中使用此脚本：

I already use this script in ms sql server :

SET IDENTITY_INSERT my_table ON
INSERT INTO my_table (
  my_identity_field,
  field1,
  field2
) 
SELECT 
  fieldA,
  fieldB
FROM
  my_other_table
WHERE
  fieldC = some_value
SET IDENTITY_INSERT my_table OFF

问题：
PostgreSQL中的脚本 SET IDENTIFY_INSERT [table_name] ON / OFF是什么？

Question : What is the script 'SET IDENTIFY_INSERT [table_name] ON/OFF' in PostgreSQL ?

谢谢

已解决：

INSERT INTO my_table (
  my_identity_field,
  field1,
  field2
) 
SELECT 
  fieldA,
  fieldB
FROM
  my_other_table
WHERE
  fieldC = some_value;

SELECT 
setval(pg_get_serial_sequence('my_table', 'my_identity_field'), 
(SELECT MAX(my_identity_field) FROM my_table));

注意：
my_table和 my_identity_field位于小写的
如果它们不是小写的（全部字母），我会报错

Note: 'my_table' and 'my_identity_field' in lowercase I'm getting error if they're not in lowercase (all letters)

推荐答案

在Postgres中需要 set identity_insert 。

You don't need set identity_insert in Postgres.

只需将数据插入表中即可。

Just insert the data into your table.

但是，您需要做的是使用 setval（）重新同步序列（自动增量）列后面的序列。 ）函数：

What you need to do however, is to re-sync the sequences that's behind your serial ("auto increment") column using the setval() function:

select setval(pg_get_serial_sequence('my_table', 'my_serial_column'), 
              (select max(my_serial_column) from my_table) 
       ); 

如果该列未定义为序列，但仅具有从序列中获取的默认值，您需要手动提供序列名称

If the column is not defined as a serial but "only" has a default value taken from a sequence, you need to supply the sequence name "manually"

select setval('my_sequence_name', (select max(my_serial_column) 
                                   from my_table)
       ); 

编辑

这是一个SQLFiddle示例： http://sqlfiddle.com/#!15/690ea/1

Here is an SQLFiddle example: http://sqlfiddle.com/#!15/690ea/1

这篇关于SET IDENTITY_INSERT PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！