1

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?

kamaci
  • 72,915
  • 69
  • 228
  • 366
  • 1
    When you use SERIAL, a sequence is automatically created. Just use nextval() on that sequence, which would be accounts_id_seq by default given your table & column names. If you want the id column to use your sequence instead, use ALTER TABLE to change the default value for the id column. (But I would just use the auto-created sequence.) – AdamKG Sep 08 '21 at 19:03
  • I want to implement a logic at my backend code. How can I know the generated sequence for that table or can I alter it to use my sequence? – kamaci Sep 08 '21 at 19:09
  • Both are possible. See [this answer](https://stackoverflow.com/a/3698777/16361) for using the existing sequence, or the ALTER TABLE docs for how to change a column default value. – AdamKG Sep 08 '21 at 19:54
  • Why? If you are pulling from the same sequence in each case, why not just let the `id` come from the column `DEFAULT next_val()`? – Adrian Klaver Sep 08 '21 at 20:06

2 Answers2

2

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.

  • It's vexing that serial is discouraged in favor of identity, but serial has much better documentation. The [serial docs](https://www.postgresql.org/docs/13/datatype-numeric.html#DATATYPE-SERIAL) point you to look at [CREATE TABLE](https://www.postgresql.org/docs/13/sql-createtable.html) for info on identity columns, but there is very little information to be found. – bfris Sep 08 '21 at 21:00
  • Yes there is. In the CREATE TABLE section it is in the links [INSERT](https://www.postgresql.org/docs/current/sql-insert.html) and [CREATE SEQUENCE)(https://www.postgresql.org/docs/current/sql-createsequence.html). Further information here [ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html) An `identity` column is a SQL compliant wrapper around a `sequence` that more tightly binds the `sequence` to the ..
     – Adrian Klaver Sep 08 '21 at 22:32
  • Does `pg_get_serial_sequence` works for `identity` too? – kamaci Sep 08 '21 at 22:47
  • 1
    @kamaci: yes, this works for identity columns as well –  Sep 09 '21 at 05:02
0

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.

bfris
  • 5,272
  • 1
  • 20
  • 37