By Sreesha


2012-09-25 05:25:00 8 Comments

I have a table which has a primary key column "gid" and its type is "Integer NOT NULL". I want to convert it into "Serial NOT NULL" so that I can insert some values into this table. I used following commands to convert it into serial:

CREATE SEQUENCE test_table_gid_seq
        INCREMENT 1
        MINVALUE 1
        MAXVALUE 2147483648 START 1
        CACHE 1;

ALTER TABLE test_table ALTER COLUMN gid
        SET DEFAULT nextval('test_table_gid_seq'::regclass);

This command converted integer to serial. But while I entered some data to the table following error occurred:

ERROR: duplicate key value violates unique constraint "pk_test".

Please help me to solve this. Is there any other method to convert integer to serial?

1 comments

@drnextgis 2012-09-25 05:53:22

Select max value of gid column (max_gid):

SELECT max(gid) FROM test_table;

And use it as start point for sequence (probably, max_gid+1):

ALTER SEQUENCE test_table_gid_seq RESTART WITH max_gid;

@jangorecki 2016-01-08 19:40:32

still not easy wrapper?

Related Questions

Sponsored Content

24 Answered Questions

[SOLVED] How can I drop all the tables in a PostgreSQL database?

  • 2010-07-24 23:24:05
  • AP257
  • 692659 View
  • 1070 Score
  • 24 Answer
  • Tags:   postgresql

9 Answered Questions

[SOLVED] How to exit from PostgreSQL command line utility: psql

1 Answered Questions

2 Answered Questions

[SOLVED] How to retrieve Postgresql Sequence-cache value from Postgresql Catalog tables?

  • 2013-08-06 16:07:34
  • Ravikumar S
  • 2751 View
  • 1 Score
  • 2 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] PostgreSql migration error

1 Answered Questions

[SOLVED] Insert from Select Postgresql with primary key constraint

  • 2014-04-16 22:31:47
  • Jeff S
  • 2157 View
  • 1 Score
  • 1 Answer
  • Tags:   sql postgresql

1 Answered Questions

[SOLVED] Postgresql - change foreign key to another column

  • 2013-11-19 17:11:28
  • NoName
  • 1113 View
  • 1 Score
  • 1 Answer
  • Tags:   sql postgresql

1 Answered Questions

[SOLVED] How can I add a 2nd serial integer key column to a table? (postgresql)

  • 2011-05-31 15:56:49
  • dan
  • 253 View
  • 1 Score
  • 1 Answer
  • Tags:   postgresql

2 Answered Questions

[SOLVED] How to update unique values in SQL using a PostgreSQL sequence?

Sponsored Content