Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7ACDA632384 for ; Sat, 10 Oct 2009 15:47:05 -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 29801-08 for ; Sat, 10 Oct 2009 18:46:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id D3266632391 for ; Sat, 10 Oct 2009 15:46:54 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n9AIkjT6017324; Sat, 10 Oct 2009 14:46:45 -0400 (EDT) To: Robert Paulsen cc: pgsql-sql@postgresql.org Subject: Re: ERROR: null value in column "id" violates not-null constraint In-reply-to: <200910101056.24051.robert@paulsenonline.net> References: <200910101056.24051.robert@paulsenonline.net> Comments: In-reply-to Robert Paulsen message dated "Sat, 10 Oct 2009 10:56:23 -0500" Date: Sat, 10 Oct 2009 14:46:45 -0400 Message-ID: <17323.1255200405@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.587 tagged_above=-10 required=5 tests=AWL=0.012, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200910/42 X-Sequence-Number: 33241 Robert Paulsen 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