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.
Watch video Serial Data Type online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 12 July 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,695 once and liked it 36 people.