Insert row into table with only a primary key field

Question

Googling around told me to try this:

insert into Plan default values;

but I got:

null value in column "id" violates not-null constraint

or this:

insert into Plan (id) values (null);

but I got the same thing.

The examples were for SQL: is there something different for PostgreSQL?

Table structure:

  create table Plan (
    id int4 not null,
    primary key (id)
);

"*The examples were for SQL*" - does not make sense. Postgres **is** using SQL. — , Feb 04 '15 at 07:06

score 3 · Answer 1 · edited May 23 '17 at 11:43

Your column does not accept NULL values (defined not null), and you did not define a default, either. So you must provide an integer value:

insert into plan (id) values (1235); -- any integer value

Most probably you really want a serial column, which draws a default value from a sequence:

Auto increment SQL function

Your table could look like this:

CREATE TABLE plan (plan_id serial PRIMARY KEY);

An the INSERT could be:

INSERT INTO plan DEFAULT VALUES;

score 1 · Accepted Answer · answered Feb 04 '15 at 05:11

You should create a sequence and set the default as nextval like so:

create sequence plan_sequence
start 100
increment 1
;

  create table plan (
    id int4 not null default nextval('plan_sequence'),
    primary key (id)
);

A sequence provides an auto-incrementing value. It is used for primary keys, etc.

Insert row into table with only a primary key field

2 Answers2

Linked