# BIGSERIAL, SMALLSERIAL, and SERIAL Data Types in PostgreSQL

> In PostgreSQL, the main difference between the BIGSERIAL, SMALLSERIAL, and SERIAL is the range of numbers they can hold/store.

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?

---
[View this page online](https://www.commandprompt.com/education/bigserial-smallserial-and-serial-data-types-in-postgresql/)