[SOLVED] How to create a PostgreSQL Table id Field and Define a Custom Sequence for It?

How to create a PostgreSQL Table id Field and Define a Custom Sequence for It?

My table is defined as:

CREATE TABLE accounts IF NOT EXISTS (
   id SERIAL PRIMARY KEY, 
   name VARCHAR(100) NOT NULL
);

At some circumstances, I want to fetch next val of id before inserting data into a table at PostgreSQL. So, I created a sequence for that:

CREATE SEQUENCE IF NOT EXISTS accounts_sequence

I can fetch the next val from sequence and then use it at insert. However, it needs to be auto incremented by same sequence if I insert a row without providing the id.

How can I create a PostgreSQL table id field and define a custom sequence for it?

Solution

A serial column will automatically create a sequence in the background.

If you want to manually call nextval() you can use pg_get_serial_sequence() to obtain the sequence name:

 select nextval(pg_get_serial_sequence('accounts', 'id'));

Note that the use of serial is discouraged in modern Postgres versions in favor of identity columns.