How to insert 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)
);

score 116 · Accepted Answer · answered Oct 11 '12 at 09:30

116

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

answered Oct 11 '12 at 09:30

Craig Ringer

307,061
76
688
778

1

both of these style doesn't work for me. I also tried with Insert into table_name values() – Nouman Bhatti Aug 03 '17 at 07:56
3

@MuhammadNouman Then you're getting something wrong or what you're using isn't PostgreSQL. Please post a new question. Put a link in it pointing to this one for context if you like. – Craig Ringer Aug 03 '17 at 08:56
thank you for `DEFAULT` keyword prompt – Roman Kazakov Jan 25 '22 at 11:14

score 6 · Answer 2 · edited Dec 26 '15 at 10:28

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)

score 5 · Answer 3 · edited Sep 26 '18 at 13:37

5

These query work for me:

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

edited Sep 26 '18 at 13:37

sideshowbarker

81,827
26
193
197

answered Sep 26 '18 at 13:34

Hiram Walker

129
2
2

4

Is `all columns without id serial` a special keyword? – domids Jun 27 '19 at 13:20
2

@Snozzlebert no this was just the poster's shorthand. Basically means that in the column list you include any column that is not of type serial, and in the select list the same. – Sean Coley Jun 16 '20 at 16:04

score 2 · Answer 4 · answered May 13 '20 at 16:51

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...

Super Kai - Kazuya Ito · Answer 5 · 2022-09-29T23:58:11.623

1

For example, you create "person" table with "id" of serial and "name" as shown below:

CREATE TABLE person (
  id serial PRIMARY KEY,
  name VARCHAR(50)
)

Then, you can use DEFAULT for "id" of serial and insert rows without column(field) names as shown below:

INSERT INTO person VALUES (DEFAULT, 'John'), (DEFAULT, 'Tom');

postgres=# SELECT * FROM person;
 id | name
----+------
  1 | John
  2 | Tom

edited Sep 29 '22 at 23:58

answered Sep 29 '22 at 23:40

Super Kai - Kazuya Ito

22,221
10
124
129

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

5 Answers5