CODEWITHSUNDEEP
sqlpostgresql
hrishikeshp19
hrishikeshp19

Reputation: 9036

Postgres: generate IDs automatically

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.

Upvotes: 0

Views: 606

Answers (2)

Houari
Houari

Reputation: 5651

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

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95751

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.

Upvotes: 3

Related Questions