1

I built a db with serial type for the pks, I migrated to another server and the pk columns are now integer and as a result I cannot add new data due to the not null restriction of a pk. Is there any Alter command which can fix this?

Spatial Digger
  • 1,883
  • 1
  • 19
  • 37

1 Answers1

1

SERIAL is not a data type in postgresql just a convenience word when creating tables that makes the column an integer type and adds auto-incrementing. All you have to do is add back auto-incrementing (a sequence) to the column and make sure its next value is greater than anything in the table.

This question covers adding serial to an existing column

This answer explains how to reset the counter

bspates
  • 382
  • 1
  • 10
  • My choosen solution: SELECT MAX(ticket_id) + 1 FROM tickets_ticket; CREATE SEQUENCE tickets_ticket_a_seq START WITH 25; ALTER TABLE tickets_ticket ALTER COLUMN ticket_id SET DEFAULT nextval('tickets_ticket_a_seq'); – Spatial Digger May 26 '19 at 21:08