Saturday, February 25, 2017


1. To convert an existing integer column to auto-increment type 'serial' : 
# Create a sequence called TABLE_id_seq (replace TABLE with actual table name). Find the maximum number and start the sequence with the next number. For example say max(id) is 30, then start with 31. 
Command: create sequence TABLE_id_seq start with 31;
Anytime you can restart the sequence with 'restart with' option. 

#Alter the table's column with default nextval function which will increment the integer when new rows are inserted into the table. 
Command: alter table TABLE alter column COLUMN set default nextval('TABLE_id_seq');

2. To copy a table to a new one use commands:
 create table NewTable as select * from OldTable;
 alter table OldTable rename to Newname;

3. To rename a column:
  alter table Table rename column Column to Newname