Search code examples
sqldatabasepostgresqlauto-incrementpostgresql-serial

How to insert a record into a table with a column declared with the SERIAL function

My database is using PostgreSQL. One table is using the serial auto-increment macro. If I want to insert a record into the table, do I still need to specify that value, or it is be automatically assigned for me? 

CREATE TABLE dataset
(
    id serial NOT NULL,
    age integer NOT NULL,
    name character varying(32) NOT NULL,
    description text NOT NULL DEFAULT ''::text
    CONSTRAINT dataset_pkey PRIMARY KEY (id)
);
Solution

  • Using the DEFAULT keyword or by omitting the column from the INSERT list:

    INSERT INTO dataset (id, age, name, description)
VALUES (DEFAULT, 42, 'fred', 'desc');

INSERT INTO dataset (age, name, description)
VALUES (42, 'fred', 'desc');