Introduction
You may have been wondering if there’s a way to auto-increment a certain column using PostgreSQL, especially coming from a different RDBMS background.
To answer your curiosity, the answer is yes, there’s a technique for that.
When creating a table with auto increment in mind, the common technique is using the SMALL SERIAL
, SERIAL
, or BIGSERIAL
data types.
Moreover, we need to be aware that SERIAL
, isn’t a true data type, it is more of a representation for creating a unique identifier column, similar to AUTO_INCREMENT that is supported by other RDBMS.
OK, then let’s get started.
Syntax of SERIAL
CREATE TABLE tablename (columnname SERIAL)
What is SERIAL?
Serial is used to generate a sequence of integers which are often used as primary keys and it doesn’t simply create an index on the column like the other RDBMS.
Serial doesn’t make the column a primary key column unless specified. However, it is recommended to specify the column as a primary key.
It is because the primary key is the combination of a not-null constraint and a unique constraint.
Serial Pseudo Types
If you’re not familiar with pseudotypes, it is used to declare a function argument or result type.
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 Example
Let’s try to create a table of subjects that will have an id column as the SERIAL column and let’s make it a primary key too. Then let’s add two more columns a subject_name
and a subject_description
.
CREATE TABLE subjects
(
id SERIAL PRIMARY KEY,
subject_name varchar(50),
description varchar(100)
)
Now, let’s try to add some data. There are possible, two ways.
Using the DEFAULT keyword
INSERT INTO subjects (id, subject_name, description)
VALUES
(DEFAULT, 'Math I', 'Math I'),
(DEFAULT, 'Math II', 'Math II'),
(DEFAULT, 'Math III', 'Math III'),
(DEFAULT, 'Math IV', 'Math IV'),
(DEFAULT, 'Math V', 'Math V')
By ignoring the column
INSERT INTO subjects (subject_name, description)
VALUES
('Math I', 'Math I'),
('Math II', 'Math II'),
('Math III', 'Math III'),
('Math IV', 'Math IV'),
('Math V', 'Math V')
Output
Using the scripts above you’ll be able to insert two batches. See the results below.
![postgresql serial]()
The function pg_get_serial_sequence()
To get the sequence name associated with the column, we need to use this function pg_get_serial_sequence
.
/* Returns: text
* Description: gets the name of the sequence that a serial, smallserial, or bigserial column uses
*/
g_get_serial_sequence(table_name, column_name)
Now, we have an idea about pg_get_serial_sequence
function, going back to our example if we wanted to get the last id
we need to use this function and combine it with the currval
function.
SELECT currval(pg_get_serial_sequence('subjects', 'id'));
As expected, we’ll be having a result with a value of 10.
Output
![postgresql serial 2]()
Conclusion
In this post, we have seen how we can use SERIAL to auto-increment our table using PostgreSQL.
I hope you have enjoyed this article as much as I have enjoyed writing it.
Stay tuned for more.
Until next time, happy programming!