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

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

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

Solution 3:

These query work for me:

insert into <table_name> (all columns without id serial)
select (all columns without id serial)
 FROM <source> Where <anything>;

Solution 4:

Inserting multiple rows wasn't working for me in this scenario:

create table test (
  id bigint primary key default gen_id(),
  msg text not null
)

insert into test (msg)
select gs
from generate_series(1,10) gs;

because I had mistakenly marked my gen_id function IMMUTABLE.

The insert query was being optimized to only call that function once rather than 10 times. Oops...