CODEWITHSUNDEEP
pythonpostgresqlpygresql
BigDiggers
BigDiggers

Reputation: 105

How to use auto increase primary key using PyGreSQL and PostgreSQL

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.

Upvotes: 1

Views: 1689

Answers (2)

mu is too short
mu is too short

Reputation: 434665

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.

Upvotes: 3

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798666

You forgot to assign the sequence to the column.

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

Upvotes: 1

Related Questions