Postgresql INSERT is asking for primary key value?

Question

Running Postgres 9.6.6. I created a table as follows:

create table person (
id serial primary key,
name text,
role integer references role (id),
phonenumber text);

When I try to insert data as follows:

insert into person values ('This Person',2,'+15105551111');

I get this message:

ERROR: invalid input syntax for integer: 'This Person'

which suggests that Postgres is expecting a value for id. But if id is a serial primary key, shouldn't it be autopopulating and autoincrementing?

Answer 1

If you do not specify the column names, the values will be applied to the first n columns in the table. Thus 'This Person' will be applied to id, and thus the error you're getting. You can (should) specify the columns names in order to avoid this problem:

INSERT INTO person
(name, role, phonenumber) -- Here
VALUES ('This Person', 2, '+15105551111');