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.

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.

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.

select * from users;

Finally, let's add a new user to our table.

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.

select * from users;

Thanks for watching!

Setup

create table users (
  name varchar not null,
  email varchar not null unique,
  age integer
);

insert into users
values ('Sam', 'sam@example.com', 35),
('Sue', 'sue@example.com', 65),
('Carl', 'carl@example.com', 45);