Serial Data Type

Описание: What's going on behind the scenes when you declare a column with the serial data type? This episode is brought to you by Hashrocket, a consultancy with expertise in PostgreSQL - visit us at https://hashrocket.com and www.pgcasts.com


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:

```sql
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:

```sql
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:

```sql
\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.

```sql
\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.

```sql
insert into users (counter) values (23), (42), (101);
```

Now, let's look at the contents of the table:

```sql
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.