How to use auto increase primary key using PyGreSQL and PostgreSQL

Question

I have a table in my PostgreSQL:

CREATE SEQUENCE dember_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE dember (id INT NOT NULL, did VARCHAR(255) DEFAULT NULL, dnix VARCHAR(255) DEFAULT NULL, durl TEXT DEFAULT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, status BOOLEAN NOT NULL, dnickname VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));

When I want to insert a record, I using the following code:

import pg
db = pg.DB(....)
db.insert('dember',{'did':did,'dnix':dnix,'durl',durl,'created',now, 'modified':now, 'status':'true','dnickname':nickname'})

Then the insert code does not work, I get the following error:

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

It looks that I have to add {'id':number} to the value dictionary.

Any suggestions? Thanks.

mu is too short · Accepted Answer · 2012-07-17 04:40:24Z

3

You should save yourself some trouble and use serial instead of int:

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

So saying:

create table t (
    id serial not null primary key
    -- ...
)

will create id as an integer column, create a sequence for it, set the default value of id to be the next value in the sequence, and set the sequence's owner to the id column; the last bit is important, the implied

ALTER SEQUENCE t_id_seq OWNED BY t.id;

that a serial type does ensures that the sequence will be dropped when the column goes away. If you don't set the sequence's owner, you can be left with dangling unused sequences in your database.

answered Jul 17, 2012 at 4:40

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Comments

Ignacio Vazquez-Abrams · Accepted Answer · 2012-07-17 03:32:40Z

1

You forgot to assign the sequence to the column.

CREATE TABLE dember (id INT NOT NULL DEFAULT nextval('dember_id_seq'), ...

answered Jul 17, 2012 at 3:32

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

1 Comment

BigDiggers Over a year ago

CREATE TABLE dember (id INT NOT NULL DEFAULT nextval('dember_id_seq'), did VARCHAR(255) DEFAULT NULL, dnix VARCHAR(255) DEFAULT NULL, durl TEXT DEFAULT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, status BOOLEAN NOT NULL, dnickname VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));

Collectives™ on Stack Overflow

How to use auto increase primary key using PyGreSQL and PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related