From: Robert Paulsen <robert@paulsenonline.net>
To: pgsql-sql@postgresql.org
Subject: ERROR: null value in column "id" violates not-null constraint
Date: Sat, 10 Oct 2009 10:56:23 -0500
User-Agent: KMail/1.9.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200910101056.24051.robert@paulsenonline.net>

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.