CODEWITHSUNDEEP
postgresql
Valentin H
Valentin H

Reputation: 7458

Postgres requires providing Primary key explicitly though the type is serial

I have a table in postgres defined like this:

CREATE TABLE t_sample (
    sample_pk SERIAL PRIMARY KEY, 
    lis_pkref bigint NOT NULL,
    patient_pkref bigint NOT NULL,  
    sample_lis_id  varchar(50) NOT NULL
);

If I try to execute an insert query:

INSERT INTO t_sample VALUES(0, 0, 'S123' )

it fails with error: 

LINE 5: INSERT INTO t_sample VALUES(0, 0, 'S123' )
                                          ^
********** Error **********

ERROR: not valid syntax for integer: „S123“ (I've translated it)
SQL Status:22P02

This query works:

INSERT INTO t_sample VALUES(0, 0, 0, 'S123' )

Doesn't serial create the value for the column automatically like auto_increment in MySQL? How can I achieve this?

Upvotes: 0

Views: 138

Answers (2)

user330315
user330315

Reputation:

You didn't specify the columns that you want to provide a value for in your INSERT statement which is bad coding style to begin with.

Only list the columns you need and the serial will work just fine:

insert into t_sample (lis_pkref, patient_pkref, sample_lis_id)
values (0, 0, 'S123' );

Another alternative is to supply the default clause:

insert into t_sample (sample_pk, lis_pkref, patient_pkref, sample_lis_id)
values (default, 0, 0, 'S123' );

or - without mentioning the columns 

insert into t_sample
values (default, 0, 0, 'S123' );

But: not specifying the columns in the INSERT statement is going to get you in trouble in the long run. You should always explicitly list them!

Do not specify the value 0 for the serial column. Unlike MySQL, Postgres tries to store the value exactly as you supply it - MySQL silently converts e.g. a value of 0 to an auto-increment value.

The reason for the error message is that with the statement INSERT INTO t_sample VALUES(0, 0, 'S123' ) Postgres (or actually any DBMS) expects values for the first three columns of the table. In the sample table the first three columns are integers and thus 'S123' can't be inserted as it is not an integer.

Upvotes: 5

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31203

It does, but you have to tell PostgreSQL which values you are providing.

INSERT INTO t_sample (lis_pkref, patient_pkref, sample_lis_id) VALUES (0, 0, 'S123' )

If you don't, it naturally starts from the first one. It doesn't skip any columns in any case.

Upvotes: 2

Related Questions