> This page location: PostgreSQL Data Types > SERIAL
> Full Neon documentation index: https://neon.com/docs/llms.txt

# Using PostgreSQL SERIAL to Create Auto-increment Column

![PostgreSQL Serial](https://neon.com/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](https://neon.com/postgresql/postgresql-tutorial/postgresql-sequences) is a special kind of database object that generates a sequence of integers. A sequence is often used as the [primary key](https://neon.com/postgresql/postgresql-tutorial/postgresql-primary-key) column in a table.

When [creating a new table](https://neon.com/postgresql/postgresql-tutorial/postgresql-create-table), the sequence can be created through the `SERIAL` pseudo-type as follows:

```sql
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`](https://neon.com/postgresql/postgresql-tutorial/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](https://neon.com/postgresql/postgresql-tutorial/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](https://neon.com/postgresql/postgresql-tutorial/postgresql-insert), you ignore the column name or use the `DEFAULT` keyword in the [`INSERT`](https://neon.com/postgresql/postgresql-tutorial/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'));
```

```
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;
```

```
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:

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

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

```sql
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.

---

## Related docs (PostgreSQL Data Types)

- [Boolean](https://neon.com/postgresql/postgresql-tutorial/postgresql-boolean)
- [CHAR, VARCHAR, and TEXT](https://neon.com/postgresql/postgresql-tutorial/postgresql-char-varchar-text)
- [NUMERIC](https://neon.com/postgresql/postgresql-tutorial/postgresql-numeric)
- [DOUBLE PRECISION](https://neon.com/postgresql/postgresql-tutorial/postgresql-double-precision-type)
- [REAL](https://neon.com/postgresql/postgresql-tutorial/postgresql-real-data-type)
- [Integer](https://neon.com/postgresql/postgresql-tutorial/postgresql-integer)
- [DATE](https://neon.com/postgresql/postgresql-tutorial/postgresql-date)
- [TIMESTAMP](https://neon.com/postgresql/postgresql-tutorial/postgresql-timestamp)
- [Interval](https://neon.com/postgresql/postgresql-tutorial/postgresql-interval)
- [TIME](https://neon.com/postgresql/postgresql-tutorial/postgresql-time)
- [UUID](https://neon.com/postgresql/postgresql-tutorial/postgresql-uuid)
- [JSON](https://neon.com/postgresql/postgresql-tutorial/postgresql-json)
- [HSTORE](https://neon.com/postgresql/postgresql-tutorial/postgresql-hstore)
- [Array](https://neon.com/postgresql/postgresql-tutorial/postgresql-array)
- [User-defined Data Types](https://neon.com/postgresql/postgresql-tutorial/postgresql-user-defined-data-types)
- [Enum](https://neon.com/postgresql/postgresql-tutorial/postgresql-enum)
- [XML](https://neon.com/postgresql/postgresql-tutorial/postgresql-xml-data-type)
- [BYTEA](https://neon.com/postgresql/postgresql-tutorial/postgresql-bytea-data-type)
- [Composite Types](https://neon.com/postgresql/postgresql-tutorial/postgresql-composite-types)