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

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.

Robert Paulsen <robert@paulsenonline.net> writes:
> 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.

Does the table actually have a default for id anymore?  (Try looking
at it with psql's \d command.)

> 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.

pg_dump does not guarantee to produce backward-transportable dumps.
Did you pay attention to whether you got any errors while loading the
8.2 dump into 8.0?  I don't have time to test it right now, but what
I'm thinking is that an ALTER TABLE ADD DEFAULT type of command failed
during the load because of some syntax issue or other.

Another possible foot-gun is to have tried to dump from the 8.2 server
using 8.0 pg_dump.  This will generally not work; pg_dump cannot be
expected to understand system catalog layouts that are newer than it
is.  You can force it to try with the -i switch, but usually that
doesn't result in anything better than a busted dump.
        regards, tom lane

On Saturday 10 October 2009 1:46 pm, Tom Lane wrote:
> Robert Paulsen <robert@paulsenonline.net> writes:
> > 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.
>
> Does the table actually have a default for id anymore?  (Try looking
> at it with psql's \d command.)

pwvault=# \d vault               Table "public.vault" Column  |          Type           |   Modifiers
----------+-------------------------+---------------id       | integer                 | not nullarchived | boolean
           | default falseservice  | character varying(256)  | not nullcategory | character varying(16)   |userid   |
charactervarying(256)  | not nullpasswd   | character varying(256)  | not nullurl      | character varying(4096) |notes
  | text                    |
 
Indexes:   "vault_pkey" PRIMARY KEY, btree (id)

So no default for id. What should it be?

Something like one of this?
default nextval('vault_id_seq')

pwvault=# \d vault_id_seq
Sequence "public.vault_id_seq"   Column     |  Type
---------------+---------sequence_name | namelast_value    | bigintincrement_by  | bigintmax_value     |
bigintmin_value    | bigintcache_value   | bigintlog_cnt       | bigintis_cycled     | booleanis_called     | boolean
 

On Saturday 10 October 2009 2:00 pm, Robert Paulsen wrote:
>
> So no default for id. What should it be?
>
> Something like one of this?
>
>     default nextval('vault_id_seq')
>

Should have tried that before posting last message -- it worked. Thanks!

Robert Paulsen <robert@paulsenonline.net> writes:
> On Saturday 10 October 2009 2:00 pm, Robert Paulsen wrote:
>> So no default for id. What should it be?
>> default nextval('vault_id_seq')

> Should have tried that before posting last message -- it worked. Thanks!

So the next question is just what happened and whether you're missing
anything else from the original database state ...
        regards, tom lane

On Saturday 10 October 2009 3:16 pm, Tom Lane wrote:
> Robert Paulsen <robert@paulsenonline.net> writes:
> > On Saturday 10 October 2009 2:00 pm, Robert Paulsen wrote:
> >> So no default for id. What should it be?
> >> default nextval('vault_id_seq')
> >
> > Should have tried that before posting last message -- it worked. Thanks!
>
> So the next question is just what happened and whether you're missing
> anything else from the original database state ...
>
>             regards, tom lane

Well, there was one other table with a sequence as a primary key and that was 
lost also. Otherwise nothing else was wrong. I went through a dump/restore 
cycle after fixing things and it all worked.

I do have a question, though, I fixed things as indicated above: id integer DEFAULT nextval('vault_id_seq') NOT NULL,
Dump gave backid integer DEFAULT nextval('vault_id_seq'::text) NOT NULL,
That "text" seems odd. Should I change it?

Robert Paulsen <robert@paulsenonline.net> writes:
> I do have a question, though, I fixed things as indicated above: 
>     id integer DEFAULT nextval('vault_id_seq') NOT NULL,
> Dump gave back
>     id integer DEFAULT nextval('vault_id_seq'::text) NOT NULL,
> That "text" seems odd. Should I change it?

That's all there is in 8.0 ...
        regards, tom lane

On Saturday 10 October 2009 4:12 pm, Tom Lane wrote:
> Robert Paulsen <robert@paulsenonline.net> writes:
> > I do have a question, though, I fixed things as indicated above:
> >     id integer DEFAULT nextval('vault_id_seq') NOT NULL,
> > Dump gave back
> >     id integer DEFAULT nextval('vault_id_seq'::text) NOT NULL,
> > That "text" seems odd. Should I change it?
>
> That's all there is in 8.0 ...
>
>             regards, tom lane

Thanks!

On Sunday 11 October 2009 3:32 am, Dmitriy Igrishin wrote:
> Hello.
> Note, that you may use SERIAL data type and PostgreSQL will implicitly
> create sequence for you column, for example,
>     CREATE table test (
>         id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will implicitly
> create 'test_id_seq'
>         dat text
>     );
>
> Regards,
> Dmitiy Igrishin
>

I believe that's how I started, not knowing any other way, but the pg_dump 
utility spits things out in all the gory details!

Somewhere along the line the default value for the id field was lost. I at 
first suspected it happened in the dump/restore cycle when I restored the 
data back into 8.0 after dumping it with 8.2 but I reran that scenario and 
something else happened: It would NOT restore back into 8.0 at all, so that 
must not be what I actually did to get into the "lost default" situation.

Below is what 8.2 dumps out. 8.0 refuses imported that. I suppose if I had 
originally edited the 8.2 dump data to "fix" this I might have gotten into 
the mess I was in but I sure don't remember doing that.

8.2 dump data:
id integer DEFAULT nextval(('auth_id_seq'::text)::regclass) NOT NULL,

What 8.0 is happy with:
id integer DEFAULT nextval('vault_id_seq'::text) NOT NULL,

Bob

Hello!<br /><br />Please, read pg_dump(1) manual page. You will find this text in it:<br />"It is  not guaranteed that
pg_dump'soutput can be loaded into a server of<br />an older major version -- not even if the dump was taken from a 
server<br/> of  that  version. Loading a dump file into an older server may require<br />manual editing of the dump
fileto remove syntax not understood by  the<br />older server."<br /><br />Regards,<br />Dmitry Igrishin<br /><br
/><divclass="gmail_quote"> 2009/10/11 Robert Paulsen <span dir="ltr"><<a
href="mailto:robert@paulsenonline.net">robert@paulsenonline.net</a>></span><br/><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im">On
Sunday11 October 2009 3:32 am, Dmitriy Igrishin wrote:<br /> > Hello.<br /> > Note, that you may use SERIAL data
typeand PostgreSQL will implicitly<br /> > create sequence for you column, for example,<br /> >     CREATE table
test(<br /> >         id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will implicitly<br /> > create
'test_id_seq'<br/> >         dat text<br /> >     );<br /> ><br /> > Regards,<br /> > Dmitiy Igrishin<br
/>><br /><br /></div>I believe that's how I started, not knowing any other way, but the pg_dump<br /> utility spits
thingsout in all the gory details!<br /><br /> Somewhere along the line the default value for the id field was lost. I
at<br/> first suspected it happened in the dump/restore cycle when I restored the<br /> data back into 8.0 after
dumpingit with 8.2 but I reran that scenario and<br /> something else happened: It would NOT restore back into 8.0 at
all,so that<br /> must not be what I actually did to get into the "lost default" situation.<br /><br /> Below is what
8.2dumps out. 8.0 refuses imported that. I suppose if I had<br /> originally edited the 8.2 dump data to "fix" this I
mighthave gotten into<br /> the mess I was in but I sure don't remember doing that.<br /><br /> 8.2 dump data:<br /> id
integerDEFAULT nextval(('auth_id_seq'::text)::regclass) NOT NULL,<br /><br /> What 8.0 is happy with:<br /><div
class="im">idinteger DEFAULT nextval('vault_id_seq'::text) NOT NULL,<br /><br /></div>Bob<br /></blockquote></div><br
/>

On Sunday 11 October 2009 8:22 am, Dmitriy Igrishin wrote:
> Hello!
>
> Please, read pg_dump(1) manual page. You will find this text in it:
> "It is  not guaranteed that pg_dump's output can be loaded into a server of
> an older major version -- not even if the dump was taken from a  server
> of  that  version. Loading a dump file into an older server may require
> manual editing of the dump file to remove syntax not understood by  the
> older server."
>

Yes, I understand that.

I tried it only to see if that was the mistake I made in the first place 
which, if that is indeed what happened, was completely inadvertent. I have 
sever different postgress servers set up and I had temporarily moved that 
database (using dump/restore) while experimenting with the system that 
normally hosts it.

The results of my trial restore to 8.0 from an 8.2 dump were *negative* in 
that it did NOT recreate the situation I had stumbled into. As a matter of 
fact the restore failed completely instead of just dropping the DEFAULT 
attribute. There must have been more to the original screw-up, perhaps some 
editing on my part but, as I said, I don't remember doing that.

Also, the temporary setup (on 8.2) exhibited the same "missing default" error 
as the original so apparently the situation was there, unnoticed, since 
before moving the database from 8.0 to 8.2. 

After fixing up the original 8.0 database (by editing the dump data as 
discussed earlier in this thread) and doing a dump/restore to re-establish 
the temporary 8.2 setup the missing default did NOT happen on 8.2 so however 
my original problem came about is still a mystery to me.

Bob