PostgreSQL offers a Pseudo-type known as SERIAL that allows the Postgres users to create auto-incremented columns in a table. Using SERIAL Pseudo-type, you can create a sequence of integers. Postgres offers three serial pseudo-types: SERIAL, BIGSERIAL, and SMALLSERIAL. All these pseudotypes differ in storage size and range.

This Post will teach you how to create auto increment columns using SERIAL pseudotype. So, let’s start.

How to Use SERIAL Pseudo-type in Postgres?

Follow the below-given syntax at the time of table creation to define the auto-increment columns:

CREATE TABLE tab_name(
col_name SERIAL
);

How Does SERIAL Pseudo-type Work in Postgres?

Consider the below snippet:

CREATE TABLE tbl_name(
id SERIAL
);

The SERIAL Pseudo-type works according to the following principles:

• It creates sequences of integers, e.g., 1,2,3, and so on.
• As discussed earlier, the SERIAL type generates a sequence of integer values, therefore set NOT NULL constraint to avoid the NULL values.
• Last but not least, the column's sequence owner must be set. Dropping a column or table automatically deletes these IDs.

Types

Postgres offers three serial pseudo-types:

1. The SERIAL carries 4 bytes of storage size and ranges between 1 to 2147483647.
2. The SMALLSERIAL takes 2 bytes of storage size and ranges between 1 to 32767.
3. A BIGSERIAL takes up 8 bytes of storage and ranges from 1 to 9223372036854775807.

Practical Implementation of SERIAL Pseudo-type:

Several examples will be exercised in this section to explain the working of SERIAL data type:

Example #1: How to Create Auto-incremented Columns in Postgres?

Following is a sample emp_data table to be created:

CREATE TABLE emp_data(
emp_id SERIAL PRIMARY KEY,
emp_name TEXT NOT NULL,
emp_email VARCHAR(80) NOT NULL,
emp_age SMALLINT
);

Now we will insert the below-given records into the emp_data table:

INSERT INTO emp_data(emp_name, emp_email, emp_age)
VALUES ('JOE', 'joe@xyz.com', 25),
('Natie', 'natie@xyz.com', 29),
('Sasha', 'sasha@xyz.com', 26),
('Mike', 'mike@xyz.com', 22),
('JOHN', 'john@xyz.com', 27),
('JOHNSON', 'johnson@xyz.com', 22);

Six records have been inserted into the emp_data table. Let’s run the SELECT command to fetch the table’s content:

SELECT * FROM emp_data;

The output shows that the SERIAL pseudo-type auto-assigned an id to each record.

Example #2: How to Insert Values Using the DEFAULT keyword in Postgres?

You can use the DEFAULT keyword to insert a value into a column having a SERIAL pseudo-type:

INSERT INTO emp_data(emp_id, emp_name, emp_email, emp_age) 
VALUES(DEFAULT, 'AMANDA', 'amanda@xyz.com', 25);

Let’s run the below command to check the newly inserted record:

SELECT * FROM emp_data;

This way, the DEFAULT keyword assists the users in inserting the value into an auto-incremented column.

Example #3: RETURNING Clause With SERIAL Pseudo-Type

This example will show you the usage of RETURNING clause:

INSERT INTO emp_data(emp_name, emp_email, emp_age) 
VALUES('KEVIN', 'kevin@xyz.com', 27)
RETURNING emp_id;

The RETURNING clause retrieved the newly inserted emp_id.

Conclusion

PostgreSQL offers a Pseudo-type known as SERIAL. It allows Postgres users to create auto-incremented columns in a table. Using SERIAL Pseudo-type, you can create a sequence of integers. Through practical examples, this write-up explained the multiple use cases of the SERIAL data type.