Postgres Serial Primary Key
In the relational database model, the concept of a primary key is an important one to understand. A primary key is a special field in a database table that contains unique values. The PostgreSQL SERIAL pseudo-type can be used to define auto-incremented columns in tables. This pseudo-type is used frequently in the primary key column of a table. In this article, we’ll take a closer look at the Postgres SERIAL primary key and look at some examples of its use.
Be sure the following prerequisites are in place before you begin this tutorial:
- You’ll need to have PostgreSQL installed on your computer in order to follow along with the examples in the article.
- You should also have some basic knowledge of PostgreSQL.
PostgreSQL SERIAL Pseudo-type
There’s a special type of data available in PostgreSQL called a sequence. This type is used to generate unique numeric identifiers in a Postgres database column. It’s most often used in the primary key column of a table.
You can use the following syntax to create a table with a column that contains a sequence:
CREATE TABLE TABLE_NAME ( id SERIAL );
Another way to generate a sequence is by using the CREATE SEQUENCE command, which creates a new sequence number generator:
CREATE SEQUENCE table_name_id;
NOTE: A sequence can be thought of as a generator that can create IDs for your records. Postgres sequences are similar to UUIDs, which are also commonly used for primary keys.
Let’s try using that
new_table_id sequence created earlier in a
CREATE TABLE statement:
CREATE TABLE new_table (
id INT NOT NULL DEFAULT NEXTVAL('new_table_id')
Next, let’s reset the sequence for
pg_get_serial_sequence by assigning ownership to the table’s
ALTER SEQUENCE new_table_id OWNED BY new_table.id;
It’s also possible to drop or delete a sequence– just use the
DROP SEQUENCE SQL keyword followed by the sequence name.
PostgreSQL SERIAL pseudo-types
There are three SERIAL pseudo-types available in PostgreSQL:
SMALLSERIALpseudo-type is a small auto-incremented integer with a storage size of two bytes and a range of one to 32,767.
SERIALpseudo-type is an auto-incremented integer with a storage size of four bytes and a range of one to 2,147,483,647.
BIGSERIALpseudo-type is a large auto-incremented integer with a storage size of eight bytes and a range of one to 9,223,372,036,854,775,807.
PostgreSQL SERIAL example
Let’s look at a simple example of a Postgres SERIAL primary key. The following statement creates the
alphabet table with the
id column as the
CREATE TABLE alphabet (
id SERIAL PRIMARY KEY,
letter VARCHAR NOT NULL
When you insert values into this table, you can just ignore the SERIAL column or use the
For example, the following statement will insert a value into the
letter column. A value will be automatically generated in the
INSERT INTO alphabet (letter)
INSERT statement works in the same way:
INSERT INTO alphabet (id, letter)
VALUES (DEFAULT , 'A');
Now that we’ve inserted a record into our table, let’s take a look at the table contents. We’ll execute a
SELECT SQL statement to retrieve the record from the table:
SELECT * FROM alphabet;
Whether you explicitly specify the
id primary key column or not, Postgres will automatically give that initial record an ID of
id | letter
1 | A
We can see that the
id column does indeed have a value of
In some cases, you might want to get the auto-incremented sequence value automatically when you insert a new record to the table. To do this, use the
RETURNING clause in your
INSERT statement. The following statement shows how this clause is used:
INSERT INTO alphabet (letter)
INSERT 0 1
Next, we’ll use the
pg_get_serial_sequence() function and pass the sequence name to the
currval() function. This will give us the most recent value of the
id column. The following statement shows how this works:
SELECT currval(pg_get_serial_sequence('alphabet', 'id'))
This statement should return the following result, which contains the most recently inserted record:
When you’re working with data in PostgreSQL, you’ll need to know how to create and use primary keys in your tables. Because a primary key column needs to contain unique values, an auto-incremented sequence generated by the SERIAL pseudo-type is a common choice for this type of column. In this article, we provided an overview of the Postgres SERIAL primary key and looked at some examples of how to create and use this type of primary key. Using our examples as a guide, you should be able to implement Postgres SERIAL primary keys in your own database environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started