Inserting a record into a table with a column declared with the SERIAL function

Question

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

Answer 1

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

Answer 2

If you create a table with a serial column then if you omit the serial column when you insert data into the table PostgreSQL will use the sequence automatically and will keep the order.

Example:

skytf=> create table test_2 (id serial,name varchar(32)); NOTICE:  CREATE TABLE will create implicit sequence "test_2_id_seq" for serial column "test_2.id" CREATE TABLE  skytf=> insert into test_2 (name) values ('a'); INSERT 0 1 skytf=> insert into test_2 (name) values ('b'); INSERT 0 1 skytf=> insert into test_2 (name) values ('c'); INSERT 0 1  skytf=> select * From test_2;  id | name  ----+------   1 | a   2 | b   3 | c (3 rows)