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

From: Robert Paulsen <robert(at)paulsenonline(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: ERROR: null value in column "id" violates not-null constraint
Date: 2009-10-10 15:56:23
Message-ID: 200910101056.24051.robert@paulsenonline.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a database with a sequence field as a primary key in a table and can no
longer insert data into it as it gets the subject error message.

This database has been in use for well over two years without any problems
using postgresql-server-8.0.13-1.1.

Suddenly, when I attempt to add a new record to the table I get the subject
error message referencing the primary key field. I am using PHP to submit the
query as follows:

============ php output ======================
Warning: pg_query() [function.pg-query]: Query failed: ERROR: null value in
column "id" violates not-null constraint
in /srv/www/htdocs/pwvault/functions.php on line 42

Query failed: INSERT INTO vault (service, category, userid, passwd, url,
notes) VALUES ('aaa', 'bbb', 'ccc', 'ddd', 'eee', 'fff')
==========================================

I tried changing the query as follows but get the same failure:
INSERT INTO vault (id, service, category, userid, passwd, url, notes) VALUES
(DEFAULT, 'aaa', 'bbb', 'ccc', 'ddd', 'eee', 'fff')

So why isn't pgsql creating the new sequence value for me? Here is some info
from the dump command:

============== dump data =====================
CREATE TABLE vault (
id integer NOT NULL,
archived boolean DEFAULT false,
service character varying(256) NOT NULL,
category character varying(16),
userid character varying(256) NOT NULL,
passwd character varying(256) NOT NULL,
url character varying(4096),
notes text
);

CREATE SEQUENCE vault_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.vault_id_seq OWNER TO robert;

SELECT pg_catalog.setval('vault_id_seq', 342, true);

ALTER TABLE ONLY vault
ADD CONSTRAINT vault_pkey PRIMARY KEY (id);

ALTER INDEX public.vault_pkey OWNER TO robert;
============================================

POSSIBLE REASON FOR THE PROBLEM:
I dumped the data, restored it into postgresql-8.2.13-0.1. dumped it from
there and restored it back to postgresql-server-8.0.13-1.1. I now get the
same failure from both 8.0 and 8.2.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2009-10-10 18:46:45 Re: ERROR: null value in column "id" violates not-null constraint
Previous Message suso 2009-10-10 09:20:08 superusuario, auditorías y cambios de clave