PostgreSQL | Data Types | Auto-Incrementing Types (serial, etc.)

Tags:

• PostgreSQL
• Database

This page explains how to use auto-incrementing types among the data types available in PostgreSQL. Columns configured with an auto-incrementing type automatically store sequential values. PostgreSQL provides three auto-incrementing types: smallserial, serial, and bigserial.

Auto-Incrementing Types

Serial types come in three data types with different ranges of values.

Type	Size	Range	Alias
smallserial	2 bytes	1 to 32767	serial2
serial	4 bytes	1 to 2147483647	serial4
bigserial	8 bytes	1 to 9223372036854775807	serial8

When adding data to a table that contains a column configured with an auto-incrementing type, do not directly specify a value for that column; let the default value be used. PostgreSQL then automatically stores a value greater than the values registered so far, generally one greater. This is similar to setting AUTO_INCREMENT on a column in MySQL.

Auto-incrementing types are implemented internally using sequences.

Create the following table for practice.

mydb=# create table myfriends (id serial, name varchar(10), address varchar(10));
CREATE TABLE
mydb=#

Then add data to the table. Because the id column’s data type is the auto-incrementing type serial, add data without specifying a value for id.

mydb=# insert into myfriends (name, address) values ('Yunho', 'Goyang');
INSERT 0 1
mydb=#

Now retrieve the data from the myfriends table.

mydb=# select * from myfriends;
 id | name  | address
----+-------+---------
  1 | Yunho | Goyang
(1 row)


mydb=#

The first value, 1, has been stored in the id column. Add three more rows.

mydb=# insert into myfriends (name, address) values ('Seonah', 'Bucheon'), ('Yongtae', 'Seoul'), ('Dongeog', 'Gangnam');
INSERT 0 3
mydb=#

Retrieve the data from the myfriends table again.

mydb=# select * from myfriends;
 id |  name   | address
----+---------+---------
  1 | Yunho   | Goyang
  2 | Seonah  | Bucheon
  3 | Yongtae | Seoul
  4 | Dongeog | Gangnam
(4 rows)


mydb=#

The id column automatically contains sequential values, and the values 2, 3, and 4 have been stored. In this way, when an auto-incrementing data type is configured for a column, sequential values are automatically stored as default values if no value is specified. More precisely, a value greater than the last stored value is stored; it is not guaranteed to always be strictly sequential.

Adding Data by Specifying a Value in an Auto-Incrementing Column

If you do not specify a value for a column configured with an auto-incrementing type, sequential values are automatically stored as default values, but you can also add data by specifying any value.

With four rows currently added, if you add the next row, the next default value, 5, will be stored in the auto-incrementing id column.

Here, specify a value for the id column and add data.

mydb=# insert into myfriends values (7, 'Yunjo', 'Paris');
INSERT 0 1
mydb=# select * from myfriends;
 id |  name   | address
----+---------+---------
  1 | Yunho   | Goyang
  2 | Seonah  | Bucheon
  3 | Yongtae | Seoul
  4 | Dongeog | Gangnam
  7 | Younjo  | Paris
(5 rows)


mydb=#

After adding the data and retrieving it from the table, the specified value is stored as-is. In this way, you can also add data by specifying a value for a column configured with an auto-incrementing type.

Now add data again using the default value without specifying the id column, and check what value goes into the id column.

mydb=# insert into myfriends (name, address) values ('Sueun', 'Yongin');
INSERT 0 1
mydb=# select * from myfriends;
 id |  name   | address
----+---------+---------
  1 | Yunho   | Goyang
  2 | Seonah  | Bucheon
  3 | Yongtae | Seoul
  4 | Dongeog | Gangnam
  7 | Younjo  | Paris
  5 | Sueun   | Yongin
(6 rows)


mydb=#

After adding the data and retrieving it from the table, the id column contains 5, which was originally the next value to be inserted.

Now add two more rows using default values for the id column.

mydb=# insert into myfriends (name, address) values ('Hansol', 'Seocho'), ('Yujin', 'Unknown');
INSERT 0 2
mydb=# select * from myfriends;
 id |  name   | address
----+---------+---------
  1 | Yunho   | Goyang
  2 | Seonah  | Bucheon
  3 | Yongtae | Seoul
  4 | Dongeog | Gangnam
  7 | Younjo  | Paris
  5 | Sueun   | Yongin
  6 | Hansol  | Seocho
  7 | Yujin   | Unknown
(8 rows)


mydb=#

The id column contains 6 and 7 after the previously stored value 5. Although a row with the value 7 had already been manually added to the id column, sequential values were stored regardless of whether duplicate values existed.

As shown here, you can add data by specifying a value in a column configured with an auto-incrementing type, but note that the specified value is not reflected in the automatically generated values.

–

This page explained how to use auto-incrementing types among the data types available in PostgreSQL.