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?

score 8 · Accepted Answer · answered Mar 24 '18 at 19:37

8

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');

answered Mar 24 '18 at 19:37

Mureinik

297,002
52
306
350

ok, but I have another table `role` with an `id` primary key and only one other column `role`, and i can successfully add data with e.g. `insert into role values ('XYZ');`. So does this only apply to tables with more than one non-(primary key) column? – sigil Mar 24 '18 at 19:47
1

No. In the table you mention, `id` is probably the second column. – Laurenz Albe Mar 25 '18 at 04:49

score 7 · Answer 2 · answered Mar 24 '18 at 19:45

If you look at the table definition, you'll see that the column is actually defined like this:

id integer DEFAULT nextval('person_id_seq'::regclass)

This is because serial is only a shorthand.

That means that the auto-generated value is only used if no value is explicitly inserted. But since your INSERT statement has no column list, you actually insert to all columns, so the first value will be inserted into the first column, which leads to the error.

You can either explicitly list the columns:

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

or you can use DEFAULT to specify that the default value should be used:

INSERT INTO person
   VALUES (DEFAULT, 'This Person', 2, '+15105551111');

The first solution is better because it is always good to be explicit about the columns (tables can get altered).

see my comment on Mureinik's answer – sigil Mar 24 '18 at 19:48 — sigil, Mar 24 '18 at 19:48

Postgresql INSERT is asking for primary key value?

2 Answers2