CODEWITHSUNDEEP
sqlpostgresqlsql-insert
sigil
sigil

Reputation: 9556

Postgresql INSERT is asking for primary key value?

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?

Upvotes: 10

Views: 14764

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248135

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).

Upvotes: 8

Mureinik
Mureinik

Reputation: 312257

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

Upvotes: 9

Related Questions