PostgreSQL – How to Auto-Generate Sequences When Importing from SQL Server

postgresqlsequencesql server

As the title states I'm switching from SQL Server to Postgresql via SQL dump. The tables and views have been migrated correctly, alas the IDENTITY columns have not been properly converted into SEQUENCEs.

In short, is there a way to auto-generate or script the generation of those sequences using the highest integer value in the ID column?

Thanks, I'm looking forward to any pointers and suggestions, and sorry if this question has already been answered elsewhere!

Julian

P.S. I'm running Postgresql 9.3.6 on Debian Wheezy

Best Answer

In CREATE table you can use bigserial/serial type 

CREATE TABLE xx (
 id  serial,
 data text
);

if you need to create sequence then CREATE SEQUENCE serial START 1;

"Simple" way to import data from MSSQL is following CREATE TABLE and replace IDENTITY with serial Import all data. If you dont import id values, Postgresql replaces them with with new values. Example

INSERT into xx (data) VALUES ('test') ;

This creates new id for row

 INSERT into xx (id, data) VALUES (1, 'test') ;

this one does not. So import data with id and run

 select setval('xx_id_seq', max(id)) FROM xx

xx_id_seq is default naming convention where xx is table name id is column and seq id for sequence ( in my system pgadmin3 creates table with that logic)

If you want to automate whole process you need to write lot of code which reads INFORMATION_SCHEMA and does dynamic SQL , but i assume that it is not the question.

For more information see manual

sequences see : http://www.postgresql.org/docs/9.3/static/sql-createsequence.html

manipulating sequnces see: http://www.postgresql.org/docs/9.3/static/functions-sequence.html

datatypes see: http://www.postgresql.org/docs/9.3/static/datatype.html

Related Question