Add auto increment column in PostgreSQL

In PostgreSQL, we cannot just add an column and mark it as auto increment like in MySQL or SQL Server. Instead, we have to create an sequence and link it to the specified column.
1. Assume that we have a table called [testtbl] with an unique column called [id]
2. Generate sequence

1CREATE SEQUENCE <Sequence name>

==>

1CREATE SEQUENCE <testtbl_id_seq>

※After the sequence’s already created, we can call NEXTVAL(‘<Sequence name>’) to generate a new value automatically.

3. Link the sequence to the unique column 

 

1	ALTER TABLE <Table name>

2	ALTER COLUMN <Column name>

3

SET DEFAULT NEXTVAL(<Created sequence name>);

==>

1 ALTER TABLE testtbl 2 ALTER COLUMN id 3 SET DEFAULT NEXTVAL('testtbl_id_seq');