PostgreSQL Auto Increment Using SERIAL

Introduction

You may have been wondering if there’s a way to auto-increment a certain column using PostgreSQL, especially coming from a different RDBMS background. 

To answer your curiosity, the answer is yes, there’s a technique for that.

When creating a table with auto increment in mind, the common technique is using the SMALL SERIAL, SERIAL, or BIGSERIAL data types.

Moreover, we need to be aware that SERIAL, isn’t a true data type, it is more of a representation for creating a unique identifier column, similar to AUTO_INCREMENT that is supported by other RDBMS.

OK, then let’s get started.

Syntax of SERIAL

CREATE TABLE tablename (columnname SERIAL)

What is SERIAL?

Serial is used to generate a sequence of integers which are often used as primary keys and it doesn’t simply create an index on the column like the other RDBMS.

Serial doesn’t make the column a primary key column unless specified. However, it is recommended to specify the column as a primary key.

It is because the primary key is the combination of a not-null constraint and a unique constraint.

Serial Pseudo Types

If you’re not familiar with pseudotypes, it is used to declare a function argument or result type.

Name	Storage Size	Range
SMALLSERIAL	2 bytes	1 to 32,767
SERIAL	4 bytes	1 to 2,147,483,647
BIGSERIAL	8 bytes	1 to 9,223,372,036,854,775,807

PostgreSQL Serial Example

Let’s try to create a table of subjects that will have an id column as the SERIAL column and let’s make it a primary key too. Then let’s add two more columns a subject_name and a subject_description.

CREATE TABLE subjects
(
    id SERIAL PRIMARY KEY,
    subject_name varchar(50),
    description varchar(100)
)

Now, let’s try to add some data. There are possible, two ways.

Using the DEFAULT keyword

INSERT INTO subjects (id, subject_name, description)
VALUES 
(DEFAULT, 'Math I', 'Math I'),
(DEFAULT, 'Math II', 'Math II'),
(DEFAULT, 'Math III', 'Math III'),
(DEFAULT, 'Math IV', 'Math IV'),
(DEFAULT, 'Math V', 'Math V')

By ignoring the column

INSERT INTO subjects (subject_name, description)
VALUES 
('Math I', 'Math I'),
('Math II', 'Math II'),
('Math III', 'Math III'),
('Math IV', 'Math IV'),
('Math V', 'Math V')

Output

Using the scripts above you’ll be able to insert two batches. See the results below.

The function pg_get_serial_sequence()

To get the sequence name associated with the column, we need to use this function pg_get_serial_sequence.

/* Returns: text
 * Description: gets the name of the sequence that a serial, smallserial, or bigserial column uses
 */
g_get_serial_sequence(table_name, column_name)  

Now, we have an idea about pg_get_serial_sequence function, going back to our example if we wanted to get the last id we need to use this function and combine it with the currval function.

SELECT currval(pg_get_serial_sequence('subjects', 'id'));

As expected, we’ll be having a result with a value of 10.

Output

Conclusion

In this post, we have seen how we can use SERIAL to auto-increment our table using PostgreSQL.

I hope you have enjoyed this article as much as I have enjoyed writing it.

Stay tuned for more.

Until next time, happy programming!