4

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

1 Answer 1

1

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

1
  • 1
    thank you, select setval('xx_id_seq', max(id)) FROM xx was what I was looking for. and, yea, I know there's gonna be a lot of scripting ahead ;) Mar 16, 2015 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.