Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 5CBCE6333D0 for ; Sun, 11 Oct 2009 10:22:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 88646-02 for ; Sun, 11 Oct 2009 13:22:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f218.google.com (mail-ew0-f218.google.com [209.85.219.218]) by mail.postgresql.org (Postfix) with ESMTP id AA52562DBE2 for ; Sun, 11 Oct 2009 10:22:08 -0300 (ADT) Received: by ewy18 with SMTP id 18so2687031ewy.19 for ; Sun, 11 Oct 2009 06:22:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=UZbSGXjKi0HKTldhw98jQapg76f/KjhCY8g0iWs3xSU=; b=v+fX8eHxdmv8KL+CgLDDGczNFb0d9pV+t1wFnO/cdDFAqtj4On/2ci5tPQaxopqaSv aptsk1Gtx9ERZaHy/FM3q1WalqFfriZy+VyBDJcojkI0xAQc35YjHeFOrHs4FZH8mT7i qNVlMm+SfEla59C1ObZWKzl1+F764pNwhfQG4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=gf6I99OiG07CzysLAGNbgdP0m0gegEOgloLc5VZ0SdFoe3EbbyvuD+hrqYuqEbgnYf l0YJzvme1XWyP4XU8IwPB23UqJEuISYSbXIvlMYLRLG7mkHh62+6wwlMGDtHaNzBAX+U +7ULynqjTeUV6fkpO7hMvXQ+CPooAzByNQUDE= MIME-Version: 1.0 Received: by 10.211.171.12 with SMTP id y12mr2881438ebo.51.1255267326311; Sun, 11 Oct 2009 06:22:06 -0700 (PDT) In-Reply-To: <200910110735.58353.robert@paulsenonline.net> References: <200910101056.24051.robert@paulsenonline.net> <200910101736.46449.robert@paulsenonline.net> <200910110735.58353.robert@paulsenonline.net> Date: Sun, 11 Oct 2009 17:22:06 +0400 Message-ID: Subject: Re: ERROR: null value in column "id" violates not-null constraint From: Dmitriy Igrishin To: Robert Paulsen Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001636d34784153c010475a8b39f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/51 X-Sequence-Number: 33250 --001636d34784153c010475a8b39f Content-Type: text/plain; charset=ISO-8859-1 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." Regards, Dmitry Igrishin 2009/10/11 Robert Paulsen > 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 > --001636d34784153c010475a8b39f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hello!

Please, read pg_dump(1) manual page. You will find this text = in it:
"It is=A0 not guaranteed that pg_dump's output can be lo= aded into a server of
an older major version -- not even if the dump was= taken from a=A0 server
of=A0 that=A0 version. Loading a dump file into an older server may require=
manual editing of the dump file to remove syntax not understood by=A0 t= he
older server."

Regards,
Dmitry Igrishin

2009/10/11 Robert Paulsen <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,
> =A0 =A0 CREATE table test (
> =A0 =A0 =A0 =A0 id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will imp= licitly
> create 'test_id_seq'
> =A0 =A0 =A0 =A0 dat text
> =A0 =A0 );
>
> Regards,
> Dmitiy Igrishin
>

I believe that's how I started, not knowing any other way, but th= e 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<= br> 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<= br> 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" si= tuation.

Below is what 8.2 dumps out. 8.0 refuses imported that. I suppose if I had<= br> originally edited the 8.2 dump data to "fix" this I might have go= tten 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 NUL= L,

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

Bob

--001636d34784153c010475a8b39f--