---
title: 'Using PostgreSQL SERIAL to Create Auto-increment Column'
page_title: 'Using PostgreSQL SERIAL to Create Auto-increment Columns'
page_description: 'In this tutorial, you will learn how to use the PostgreSQL SERIAL to create an auto-increment column in a database table.'
prev_url: 'https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-serial/'
ogImage: '/postgresqltutorial/PostgreSQL-Serial-268x300.png'
updatedOn: '2024-02-01T08:57:58+00:00'
enableTableOfContents: true
previousLink:
  title: 'PostgreSQL CREATE TABLE AS'
  slug: 'postgresql-tutorial/postgresql-create-table-as'
nextLink:
  title: 'PostgreSQL Sequences'
  slug: 'postgresql-tutorial/postgresql-sequences'
---

![PostgreSQL Serial](/postgresqltutorial/PostgreSQL-Serial-268x300.png?alignright)**Summary**: in this tutorial,  you will learn about the PostgreSQL `SERIAL` pseudo\-type and how to use the `SERIAL` pseudo\-type to define auto\-increment columns in tables.

## Introduction to the PostgreSQL SERIAL pseudo\-type

In PostgreSQL, a [sequence](postgresql-sequences) is a special kind of database object that generates a sequence of integers. A sequence is often used as the [primary key](postgresql-primary-key) column in a table.

When [creating a new table](postgresql-create-table), the sequence can be created through the `SERIAL` pseudo\-type as follows:

```phpsql
CREATE TABLE table_name(
    id SERIAL
);
```

By assigning the `SERIAL` pseudo\-type to the `id` column, PostgreSQL performs the following:

- First, create a sequence object and set the next value generated by the sequence as the default value for the column.
- Second, add a [`NOT NULL`](postgresql-not-null-constraint) constraint to the `id` column because a sequence always generates an integer, which is a non\-null value.
- Third, assign the owner of the sequence to the `id` column; as a result, the sequence object is deleted when the `id` column or table is dropped

Behind the scenes, the following statement:

```sql
CREATE TABLE table_name(
    id SERIAL
);
```

is equivalent to the following statements:

```sql
CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
    id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;
```

PostgreSQL provides three serial pseudo\-types `SMALLSERIAL`, `SERIAL`, and `BIGSERIAL` with the following characteristics:

| **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 examples

Let’s take some examples of using the SERIAL columns.

### 1\) Basic PostgreSQL SERIAL example

It is important to note that the `SERIAL` does not implicitly [create an index](../postgresql-indexes/postgresql-create-index) on the column or make the column the [primary key](postgresql-primary-key) column. However, this can be done easily by specifying the `PRIMARY KEY` constraint for the `SERIAL` column.

The following statement creates the `fruits` table with the `id` column as the `SERIAL` column:

```sql
CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);
```

To assign a default value to a serial column when [inserting a row into the table](postgresql-insert), you ignore the column name or use the `DEFAULT` keyword in the [`INSERT`](postgresql-insert) statement. For example:

```sql
INSERT INTO fruits(name)
VALUES('Orange');
```

Or

```sql
INSERT INTO fruits(id,name)
VALUES(DEFAULT,'Apple');
```

PostgreSQL inserted two rows into the `fruits` table with the values for the `id` column are 1 and 2\.

```sql
SELECT * FROM fruits;
```

```text
 id |  name
----+--------
  1 | Apple
  2 | Orange
(2 rows)
```

### 2\) Getting the sequence name

To get the sequence name of a `SERIAL` column in a table, you use the `pg_get_serial_sequence()` function as follows:

```sql
pg_get_serial_sequence('table_name','column_name')
```

You can pass a sequence name to the  `currval()` function to get the recent value generated by the sequence. For example, the following statement returns the recent value generated by the `fruits_id_seq` object:

```sql
SELECT currval(pg_get_serial_sequence('fruits', 'id'));
```

```sql
currval
---------
2
(1 row)
```

### 3\) Retrieving the generated value

If you want to get the value generated by the sequence when you insert a new row into the table, you use the `RETURNING id` clause in the `INSERT` statement.

The following statement inserts a new row into the `fruits` table and returns the value generated for the id column.

```sql
INSERT INTO fruits(name)
VALUES('Banana')
RETURNING id;
```

```sql
id
----
3
(1 row)
```

The sequence generator operation is not transaction\-safe. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value.

If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence.

### 4\) Adding a serial column to an existing table

First, create a new table called `baskets` without a primary key column:

```
CREATE TABLE baskets(
    name VARCHAR(255) NOT NULL
);
```

Second, add a `SERIAL` column to the `baskets` table:

```php
ALTER TABLE baskets
ADD COLUMN id SERIAL PRIMARY KEY;
```

Third, describe the table `baskets` to verify the change:

```
\d baskets
```

Output:

```
                                    Table "public.baskets"
 Column |          Type          | Collation | Nullable |               Default
--------+------------------------+-----------+----------+-------------------------------------
 name   | character varying(255) |           | not null |
 id     | integer                |           | not null | nextval('baskets_id_seq'::regclass)
Indexes:
    "baskets_pkey" PRIMARY KEY, btree (id)
```

## Summary

- Use the PostgreSQL pseudo\-type `SERIAL` to create an auto\-increment column for a table.