Postgres: generate IDs automatically

Question

Objective: Have postgres generate ids automatically

CREATE TABLE user_privilege (
    id bigint NOT NULL,
    name character varying(255) NOT NULL,
    version integer
);
CREATE TABLE

INSERT INTO user_privilege (name, version) values ('XYZ', 1);

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

ALTER TABLE user_privilege ALTER COLUMN id SET DEFAULT nextval('user_privilege_id_seq'::regclass);

ERROR:  relation "user_privilege_id_seq" does not exist

Thanks!

EDIT:

I want to keep my id as bigint as all other tables have id as bigint.

postgresql.org/docs/current/static/…

user330315
– user330315

2014-02-11 19:10:54 +00:00
Commented Feb 11, 2014 at 19:10 — user330315
– user330315, Commented Feb 11, 2014 at 19:10

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-02-11 19:05:01Z

3

You need to use either SERIAL or BIGSERIAL, not BIGINT.

CREATE TABLE user_privilege (
    id BIGSERIAL NOT NULL,

It's not clear whether your table has a PRIMARY KEY or UNIQUE constraint. But it should.

answered Feb 11, 2014 at 19:05

Mike Sherrill 'Cat Recall'

96.6k20 gold badges132 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hrishikeshp19 Over a year ago

Thanks for the answer. Appreciate it!

Houari · Accepted Answer · 2014-02-11 19:09:00Z

0

You have to create the sequence at first:

CREATE SEQUENCE user_privilege_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

and after you can use it:

ALTER TABLE ONLY user_privilege ALTER COLUMN id SET DEFAULT nextval('user_privilege_id_seq'::regclass);

Here is the create sequence documentation

answered Feb 11, 2014 at 19:09

Houari

5,6513 gold badges33 silver badges57 bronze badges

7 Comments

Mike Sherrill 'Cat Recall' Over a year ago

SERIAL and BIGSERIAL data types automatically create a sequence. You don't have to create one manually.

Houari Over a year ago

Yes i know! but it seems that he want learn how to add sequence to a column manually.

Mike Sherrill 'Cat Recall' Over a year ago

The OP wants to generate ID numbers automatically. In PostgreSQL, the normal way to do that is to use the right data type, not to generate the sequence manually.

Houari Over a year ago

Yes, he wants to generate IDs automatically, and this can be acheved by using a sequence, and sequences can just be added explicitly or implicitly.

user330315 Over a year ago

@riship89 You might also want to associate the sequence with the column: alter sequence user_privilege_id_seq owned by user_privilege.id. Then if the table or column is dropped the sequence is also automatically dropped and a truncate .. restart identity will reset the sequence.

|

Collectives™ on Stack Overflow

Postgres: generate IDs automatically

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related