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

Question

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?

Answer 1

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.

Answer 2

So, as it's already been stated, a serial column will create a sequence for you. Further, that sequence will be automatically incremented if you leave out the id field in an INSERT query.

It sounds like for your back end code, you sometimes need to get the id value when doing an INSERT. There are two ways I know of to do this:

1. Use SELECT nextval(<mysequence>). Then inject that number into your INSERT query.
2. Use a RETURNING clause in your INSERT query. In that case the data is saved in the database and your fields are returned. I use this strategy a lot.

Using the second method, your query might look like this

INSERT INTO accounts (name) 
VALUES ('Joe Cool') 
RETURNING id;

Depending on how complicated your backend is, you might also be able to interact with two tables using a CTE. This answer has a good example of using a WITH statement to use the id from one INSERT into another INSERT all in the same query.