How to implement auto-incremented id in PostgreSQL?

2 years ago
Benjamin Taylor
2 minutes

In PostgreSQL, you can use a sequence to achieve auto-incrementing IDs. A sequence is a special database object that generates consecutive numerical values.

To achieve automatic ID incrementation, you can follow these steps:

  1. Create a sequence object.
CREATE SEQUENCE table_name_id_seq;

This will create a sequence object named table_name_id_seq.

  1. Define a column in the table to reference the sequence object.
ALTER TABLE table_name
  ADD COLUMN id SERIAL PRIMARY KEY;

This will add a column named id to the table_name table, setting it as the serial type. The SERIAL keyword will automatically create and associate a sequence object and set it as the primary key.

  1. When inserting data, the auto-increment ID will be automatically assigned.
INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);

When inserting data, PostgreSQL will automatically use a sequence object to generate the next incrementing ID value and assign it to the id column.

  1. The current value of the sequence.
SELECT currval('table_name_id_seq');

This will return the auto-increment ID value of the last inserted table_name table.

It is important to note that each time data is inserted, the sequence object will automatically increment and assign a new ID value. If you need to manually specify the ID value, you can use the nextval() function to get the next value and use it as the ID value when inserting. For example:

INSERT INTO table_name (id, column1, column2, ...)
  VALUES (nextval('table_name_id_seq'), value1, value2, ...);

This will manually assign the ID value to the next value of the sequence object.

bannerAds