Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SET IDENTITY_INSERT postgresql

Tags:

sql

postgresql

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)

like image 346
postgreat Avatar asked Jan 27 '14 08:01

postgreat


People also ask

How do you on IDENTITY_INSERT is set to ON?

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value. The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Do I need to set IDENTITY_INSERT off?

Set IDENTITY_INSERT on for all tables You will get an error that the other table is using this option if you try to set this option to ON in more than one table. It will also tell you the name of the table which is using this option. Therefore, you should always set this option to OFF after inserting the rows.

What is set IDENTITY_INSERT on SQL?

The set identity_insert command in SQL Server, as the name implies, allows the user to insert explicit values into the identity column of a table.


1 Answers

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

like image 69
a_horse_with_no_name Avatar answered Oct 29 '22 01:10

a_horse_with_no_name