BIGSERIAL, SMALLSERIAL, and SERIAL Data Types in PostgreSQL

In PostgreSQL, SERIAL is a pseudo-type type that creates a column that automatically increments its value. When you specify SERIAL as the data type of a column, PostgreSQL automatically generates unique values for that particular column. PostgreSQL supports three types of SERIAL pseudotypes: BIGSERIAL, SMALLSERIAL, and SERIAL. All these data types differ in terms of range.

This post demonstrates how to override a SERIAL column in PostgreSQL using suitable examples.

BIGSERIAL, SMALLSERIAL, and SERIAL Data Types in PostgreSQL

The main parameter that distinguishes BIGSERIAL, SMALLSERIAL, and SERIAL is the range of numbers they can hold/store. BIGSERIAL can store the biggest range of values, followed by SERIAL, and then SMALLSERIAL.

The range of BIGSERIAL is "-9223372036854775808" to "9223372036854775807"
The SERIAL data type has a range of "-2147483648" to "2147483647"
The SMALLSERIAL has a range of "-32768" to "32767".

The choice of which pseudotype to use depends entirely on the specific requirements or preferences of the user.

Consider the following steps for a profound understanding of the SERIAL data types in Postgres:

Step 1: Create Auto-Incrementing Columns

Let’s first create auto-incrementing (SERIAL) columns. For this purpose, specify the desired pseudotype for a specific column at the time of table creation:

CREATE TABLE command_prompt_1( 
bigSerial_col BIGSERIAL,
serial_col SERIAL PRIMARY KEY, 
smallSerial_col SMALLSERIAL, 
msg TEXT
);

A table named “command_prompt” with the specified SERIAL data types has been successfully created:

Step 2: Verify Column's Default Value

Execute the "\d" meta-command with the newly created table name to check the column's default value:

\d command_prompt_1;

The serial types have been set as default values of the selected columns.

Step 3: INSERT Data

Now insert a couple of records in the command_prompt table by executing the following query:

INSERT INTO command_prompt_1 (msg) 
VALUES ('blog 1'), 
('blog 2'), 
('blog 3')
RETURNING *;

The “RETURNING *” clause is utilized in the above query to get the newly inserted records. From the below-provided output snippet, you can observe that the SERIAL columns are automatically filled with the sequence of unique values:

That’s all about creating a column with SERIAL Data Types in PostgreSQL.

Conclusion

PostgreSQL supports three types of SERIAL pseudotypes: BIGSERIAL, SMALLSERIAL, and SERIAL. The main parameter that distinguishes BIGSERIAL, SMALLSERIAL, and SERIAL is the range of numbers they can hold/store. BIGSERIAL can store the biggest range of values, followed by SERIAL, and then SMALLSERIAL. The choice of which pseudotype to use totally depends on the user's needs. This write-up has demonstrated a complete guide on overriding a SERIAL column in PostgreSQL.

Related Articles: PostgreSQL SERIAL- How to Create Auto-increment Columns,
Can a User Override a SERIAL Column in PostgreSQL?

BIGSERIAL, SMALLSERIAL, and SERIAL Data Types in PostgreSQL