SET IDENTITY_INSERT postgresql

postgresql, sql

Solution

You don't need `set identity_insert` in Postgres.

Just insert the data into your table.

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

Edit

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

Problem

I'm not familiar with postgresql script. 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 ``` Question : What is the script 'SET IDENTIFY_INSERT [table_name] ON/OFF' in PostgreSQL ? Thank you SOLVED : ``` 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)); ``` Note: 'my_table' and 'my_identity_field' in lowercase I'm getting error if they're not in lowercase (all letters)

Original source