×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Paul Allies Allies
Added: Dec 20, 2013 9:32 PM
Modified: Dec 20, 2013 9:56 PM
Views: 25
Change the a column from integer to serial and start from 100
  1. CREATE SEQUENCE <table>_<column>_seq
  2.   INCREMENT 1
  3.   MINVALUE 0
  4.   MAXVALUE 9223372036854775807
  5.   START 100
  6.   CACHE 1;
  7.  
  8. ALTER TABLE <table> ALTER COLUMN <column> SET DEFAULT NEXTVAL('<table>_<column>_seq');
  9.  
  10. --Example
  11. CREATE SEQUENCE customer_id_seq
  12.   INCREMENT 1
  13.   MINVALUE 0
  14.   MAXVALUE 9223372036854775807
  15.   START 100
  16.   CACHE 1;
  17.  
  18. ALTER TABLE customer ALTER COLUMN id SET DEFAULT NEXTVAL('customer_id_seq');
  19.  
  20. --Reseed id
  21. -- We might want to delete a number of rows and reseed the id back to 100
  22. ALTER SEQUENCE customer_id_seq RESTART WITH 100;
  23.