CODEWITHSUNDEEP
sqlpostgresqlauto-increment
SIn san sun
SIn san sun

Reputation: 623

Primary key not AUTO INCREMENT in the PostgreSQL

I have table Category and I have 3 columns category_id, category_name,category_description. When I execute insert script without category_id parameter I have this error:

ERROR:  null value in column "category_id" violates not-null constraint
DETAIL:  Failing row contains (null, T_601, Yojimbo).
SQL state: 23502

This is my select script:

INSERT INTO category ( category_name, category_description)
    VALUES ('T_601', 'Yojimbo');

This is the image of my table : t

Upvotes: 1

Views: 29513

Answers (2)

user4374390
user4374390

Reputation:

Use the following to add a serial to the column category_id

CREATE SEQUENCE cateogry_id_seq;
ALTER TABLE category ALTER COLUMN category_id SET DEFAULT nextval('cateogry_id_seq');

Now the column will be auto incremented and you don't have to enter the catgory_id column in insert queries

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Change the data type to serial, which is Postgres's way of spelling auto_increment. If you have a not-NULL integer column with no default, then you will get an error when you attempt an insert.

If you assign a default, then the unique constraint (part of the primary key) will just create a duplicate key error on the second insert.

Upvotes: 8

Related Questions