# PostgreSQL SERIAL- How to Create Auto-increment Columns

> PostgreSQL offers a Pseudo-type known as SERIAL. It allows Postgres users to create auto-incremented columns in a table. Using SERIAL, you can create a sequenc…

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-serial-how-to-create-auto-increment-columns/)