From: Robert Paulsen <robert@paulsenonline.net>
To: Dmitriy Igrishin <dmitigr@gmail.com>
Subject: Re: ERROR: null value in column "id" violates not-null  constraint
Date: Sun, 11 Oct 2009 07:35:58 -0500
User-Agent: KMail/1.9.1
Cc: pgsql-sql@postgresql.org
References: <200910101056.24051.robert@paulsenonline.net>
	<200910101736.46449.robert@paulsenonline.net>
	<b4399d110910110132q58e7f211v9ba204cb47239d3c@mail.gmail.com>
In-Reply-To: <b4399d110910110132q58e7f211v9ba204cb47239d3c@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200910110735.58353.robert@paulsenonline.net>

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