If you've ever wondered what's going on behind the scenes when you declare a column with the serial data type, then this episode's for you.
Postgres Version 9.5
Now if you’ve been working with Postgres for at least a little while, you are
probably used to seeing tables created with an
id column using the
Something like this:
create table users ( id serial primary key )
We often see it with an
In this episode I want to take a look at that
serial data type and explore
what it is that happens when we define a column as
Let’s remove the
primary key part so that we can just focus on
We will also add a regular
counter integer column for comparison:
create table users ( id serial, counter integer );
That created our
users table with an
id column and a
Let’s take a look at it:
\d users Table "public.users" Column | Type | Modifiers ---------+---------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) counter | integer |
The first thing we’ll notice is that our
id column gets a type of
just like the
counter column. Whereas we explicitly declared
counter as an
serial implicitly sets
id as an integer. This is because the
serial data type is an auto-incrementing integer.
Next we will notice that
serial gives the
id column a bunch of modifiers
counter column doesn’t get.
For one, it is
not null. The
id column should always have an integer in it.
We also see that it gets a default value. This is the auto-incrementing part.
The default value is the
nextval() function called on the
This ensures that we have unique, monotonically increasing values for our
column. Every time we insert into the
users table the default value for
will be the next value in the sequence. This, of course, assumes that we always
id be set to its default value.
Ok, so our default value is based on a sequence, but where does this sequence come from? I don’t remember creating one.
Well, if we look at all the objects in our database, we’ll see that there is a sequence defined.
\d List of relations Schema | Name | Type | Owner --------+--------------+----------+--------- public | users | table | pgcasts public | users_id_seq | sequence | pgcasts
When we declared the
id column as
serial, a sequence was created for us.
Postgres named the sequence based off of the name of the table and the name of
the column, hence
If we do a couple inserts into the table, we can see the effects of
being called on this sequence.
insert into users (counter) values (23), (42), (101);
Now, let’s look at the contents of the table:
table users; id | counter ----+--------- 1 | 23 2 | 42 3 | 101
The sequence starts at 1 and counts up from there for each record.
We should now have a better idea of what happens when we declare a column as
Until next time, thanks for watching.