Add an Auto Incrementing Primary Key
How to add an auto incrementing primary key to a table in Postgres. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
In this example, we're going to be dealing with a dummy database that has a table users, with no primary key currently set.
We can inspect this table using the "\d" command.
```
\d users
```
We can also select from this table and see that we already have data.
```sql
select * from users;
```
To add an autoincrementing id as the primary key to our table, we can use the "alter table" command, passing our users table in, and then specifying that we want to add a column called id with the type "serial" and set it as our primary key.
```sql
alter table users add column id serial primary key;
```
The serial type is a Postgres shorthand for allowing us to create columns with an autoincrementing integer.
Now if we inspect our users table, we can see that there is a new column of type integer with a sequence that was generated for us by Postgres.
```
\d users
```
If we select from our users again, we can see that our existing users have already had their id populated.
```sql
select * from users;
```
Finally, let's add a new user to our table.
```sql
insert into users values ('Jamie', 'EM3456');
```
By selecting from the users table again, we can see that the new row has had the id populated appropriately.
```sql
select * from users;
```
Thanks for watching!
Watch video Add an Auto Incrementing Primary Key online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 08 August 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,875 once and liked it 14 people.