PostgreSQL - Get to know the serial data type and use it correctly
What is serial data type in PostgreSQL?
If you are new to PostgreSQL, you might run into the data type of serial and start to wonder, why is it so weird that sometimes you can be able to use it in your SQL statements, some time you can’t.
According to PostgreSQL Docs, serial is not a true type, but an autoincrementing interger. It comes in three forms: smallserial, serial and bigserial.
What does it mean by saying “not a true type”?
Well, in order to understand this, we have to know why it exists in the first place.
How serial type works in PostgreSQL?
This is a typical occasion when serial is used, in a create table statement.
CREATE TABLE tablename (
colname SERIAL
);
which is equivalent to this statement:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
As you can see, using serial data type has simplified the task of creating unique identifier columns. It generates a column with data type integer and attaches a UNIQUE or PRIMARY KEY constraint to it when we use it within the create table statement.
It’s similar to the AUTO_INCREMENT property supported by some other databases.
In plain term, you can think of serial type as the middleman to a primary key with integer data type. It exists temporarily for the sake of convenience for creating unique identifier columns.
That’s why you can only use it in a create table statement. You can’t be able to specify a serial column for an alter table statement.
What is the difference between serial, bigserial, and smallserial then?
There are three variables of serial which create three corresponding integer types:
- serial (or serial4) creates integer columns
- bigserial (or serial8) creates bigint columns
- smallserial (or serial2) creates smallint columns
Please also note that the sequence created for a serial column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force the removal of the column default expression.
Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.
