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
Message-ID: <200910101056.24051.robert@paulsenonline.net> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robert@paulsenonline.net
Subject: Re: ERROR: null value in column "id" violates not-null constraint
In-Reply-To: <200910101056.24051.robert@paulsenonline.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox