Transcript

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 serial keyword.

Something like this:

create table users ( id serial primary key )

We often see it with an id column.

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 serial.

Let's remove the primary key part so that we can just focus on serial. 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 counter column. 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 integer just like the counter column. Whereas we explicitly declared counter as an integer, 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 that the 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 users_id_seq. This ensures that we have unique, monotonically increasing values for our id column. Every time we insert into the users table the default value for id will be the next value in the sequence. This, of course, assumes that we always let the 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 users_id_seq.

If we do a couple inserts into the table, we can see the effects of nextval 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 serial.

Until next time, thanks for watching.