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.