# SQL - PostgreSQL - SERIAL Example
*Source: [Using PostgreSQL SERIAL To Create Auto-increment Column - postgresqltutorial.com](https://www.postgresqltutorial.com/postgresql-serial/)*
A walkthrough demonstrating how to use the [[PostgreSQL]] pseudo-type `SERIAL` to create an auto-increment column for a table.
## Contents
- [[#Overview|Overview]]
- [[#Example|Example]]
- [[#Notes|Notes]]
- [[#Indexing|Indexing]]
- [[#Insertion|Insertion]]
- [[#Retrieving the Sequence Object's Name|Retrieving the Sequence Object's Name]]
- [[#Transactions|Transactions]]
- [[#Appendix: Links|Appendix: Links]]
## Overview
In [[PostgreSQL]] databases, a convenient *pseudo-type* [sequence](https://www.postgresqltutorial.com/postgresql-sequences/) called `SERIAL` is provided for easily defining auto-incrementing `INTEGER` data types to columns in a table.
As a side-note, a *sequence* is a special kind of database object that generates a sequence of integers. A sequence is often used as the [primary key](https://www.postgresqltutorial.com/postgresql-primary-key/) column in a table.
## Example
For example, to create a new table with a `SERIAL` ID column you can simply run this [[SQL]] code:
```SQL
CREATE TABLE table_name(
ID SERIAL
);
```
By assigning the `SERIAL` pseudo-type to the `ID` column, [[PostgreSQL]] performs the following steps:
1. Create a sequence object and set the next value generated by the sequence as the default value for the column.
2. Add a `NOT NULL` constraint to the `ID` column because a sequence always generates an integer, which is a non-null value.
3. 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.
Therefore, the equivalent [[SQL]] without using `SERIAL` would look like this:
```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;
```
## Notes
### SERIAL Pseudo Type Characteristics
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 |
### Indexing
It is important to note that the `SERIAL` does not implicitly [create an index](https://www.postgresqltutorial.com/postgresql-indexes/postgresql-create-index/) on the column or make the column as the [primary key](https://www.postgresqltutorial.com/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
);
```
### Insertion
To assign the default value for a serial column when you [insert row into the table](https://www.postgresqltutorial.com/postgresql-insert/), you ignore the column name or use the `DEFAULT` keyword in the `INSERT` statement.
See the following 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;`
```
returns:
```SQL
id | name
----+--------
1 | Apple
2 | Orange
(2 rows)
```
### Retrieving the Sequence Object's 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'));
```
returns:
```SQL
currval
---------
2
(1 row)
```
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;`
```
returns:
```SQL
id
----
3
(1 row)
```
### Transactions
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.
***
## Appendix: Links
- [[2-Areas/Code/_README|Code]]
- [[SQL]]
- [[Databases]]
- [[PostgreSQL]]
- [[Development]]
*Backlinks:*
```dataview
list from [[SQL - PostgreSQL - SERIAL Example]] AND -"Changelog"
```