PostgreSQL Auto Increment

Introduction to PostgreSQL Auto Increment

The following article provides an outline on PostgreSQL Auto Increment. In PostgreSQL, we have a special database object called a SEQUENCE object that is a list of ordered integer values. SEQUENCE is often used when we want to describe a unique key or primary key or column which is auto-incremented in database tables. SERIAL is a pseudo-type that helps us generating a SEQUENCE object when a particular column is declared of pseudo-type.

SEQUENCE Database Object

• First you need to understand how SEQUENCE object works.
• SEQUENCE is completely a schema-bound object defined by the user according to his/her special requirements. {11, 12 ,13, 14, 15,…} is completely a different sequence than {100, 99, 98,97,..} sequence.
• A sequence can be created with the help of the CREATE SEQUENCE statement.
• We can define the minimum and maximum value, incremental step value, name, and owner of the SEQUENCE as well as cache space which needs to be pre-allocated space in the memory of the sequenced list.

SERIAL pseudo-type

• While creating a table in PostgreSQL, if we declare any column of the type SERIAL then internally the SERIAL pseudo-type also creates a new SEQUENCE object for that column and table with default values.
• That further helps us in achieving the auto-incrementation of the values of certain columns declares as of type SERIAL.

Internal Working

When we declare the column named “student_id” of type SERIAL in the table creation query for “educba” table in the following way.

Code:

CREATE TABLE educba(student_id SERIAL,name VARCHAR(100));

Then internally there are a bunch of commands being executed.

Some of them are as follows:

CREATE SEQUENCE educba_student_id_seq;

CREATE TABLE educba (student_id integer NOT NULL DEFAULT nextval('educba_student_id_seq'), name VARCHAR(100));

ALTER SEQUENCE educba_student_id_seq OWNED BY educba.student_id;

The above internal queries are executed while declaring any column of type SERIAL in the table.

• A new SEQUENCE object is created for the column student_id and the next value of that sequence is assigned to the default value of the student_id column.
• As a sequence always generates a non-null integer value the column student_id will be assigned with the NOT NULL constraint.
• Now, if the student_id column is deleted then the SEQUENCE object associated with it should also get deleted.
• For this purpose, the owner of the created sequence educba_student_id_seq is set to the student_id column of the educba table.

Types of SERIAL pseudo-types

In PostgreSQL, we are allowed to create a SERIAL pseudo-type which can belong to either of the following three types.

• SMALLSERIAL
• SERIAL
• BIGSERIAL

Which differ in their storage space and range limits. This is similar to short int, int, and long int.

The storage space and range of all the three pseudo-types of serial are as follows:

Pseudo-type name	Storage size required	Range of its values in the list
SMALLSERIAL	2 bytes of space	1 – 32,767
SERIAL	4 bytes of space	1 – 2,147,483,647
BIGSERIAL	8 bytes of space	1 – 9,223,372,036,854,775,807

Example of SERIAL pseudo-type

Let us now take an example how serial pseudo-type helps us achieve auto-incrementation. However, it is necessary to know that whenever a column is declared of the type SERIAL then it does not mean that an index is created on that column or that column is considered as its primary key. To do so, you will externally need to define the column as the PRIMARY KEY.

Code:

CREATE TABLE educba(student_id SERIAL PRIMARY KEY ,name VARCHAR(100) NOT NULL);

Output:

To verify our table creation let us fire the \dt command.

Output:

As it can be seen that the table named educba is created successfully. Now, when you insert the records in the table having auto-incremented valued columns then you can either skip inserting those column values or specify the DEFAULT keyword for those columns in your INSERT query statement. We will insert the records using both methods. Firstly, skipping the student_id column.

Code:

INSERT INTO educba(name) VALUES('Payal');

Output:

Saying that 1 row is inserted successfully. Now, let us insert a record specifying DEFAULT value for our student_id column.

Code:

INSERT INTO educba(student_id,name) VALUES(DEFAULT,'Piyush');

Output:

Saying our one more row is inserted. Now, let us check the records of our educba table and see that values are inserted for the student_id column and whether they are auto-incremented.

Code:

SELECT * FROM educba;

Output:

Hence, we can see that the student_id column works as the auto-incremented field now.

Methods of Sequence Value

Methods related to the sequence object created in the database like fetching the name of the sequence object and retrieving the current maximum value of that sequence object. pg_get_serial_sequence method is used to get the name of a sequence object created for a particular table’s serial typed column. It takes two parameters the name of the table and the name of a column of that table.

Code:

pg_get_serial_sequence('tableName','columnName');

Let us retrieve the name of the sequence object created for the student_id column.

Code:

SELECT pg_get_serial_sequence('educba','student_id');

Output:

So, educba_student_id_seq is the sequence object’s name.

Now, we will retrieve the maximum value or the last value assigned by this sequence object to the column student_id using the method currval(nameOfSequence) that takes only one parameter which is the name of the sequence object.

Code:

SELECT currval(pg_get_serial_sequence('educba', 'student_id'));

Output:

However, you need to know that the sequence value of the serial typed column is not the transaction-safe value. In two instances accessing the same table of the database insert the record then both will get different sequence value and if one of them reverts or rollback the transaction then the value which that session retrieved remains unchanged and hence creates a gap in the table values of auto-incremented fields.

Conclusion

In PostgreSQL, auto incrementation is achieved through declaring columns of pseudo-datatype SERIAL and is internally managed by the SEQUENCE database object which is kind of an ordered list.

This is a guide to PostgreSQL Auto Increment. Here we discuss the introduction to PostgreSQL auto Increment with SEQUENCE database object, types of SERIAL pseudo-types and methods of sequence value. You may also have a look at the following articles to learn more –

1. PostgreSQL Boolean
2. PostgreSQL RANK()
3. CAST in PostgreSQL
4. PostgreSQL Notify
5. PostgreSQL Trunc()