lundi 20 avril 2015

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?

Aucun commentaire:

Enregistrer un commentaire