X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 7EFAB9FB34B
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 17 Aug 2006 15:22:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 42756-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 17 Aug 2006 18:22:07 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wr-out-0506.google.com (wr-out-0506.google.com
 [64.233.184.235])
	by postgresql.org (Postfix) with ESMTP id E508D9FB25D
	for <pgsql-sql@postgresql.org>; Thu, 17 Aug 2006 15:22:06 -0300 (ADT)
Received: by wr-out-0506.google.com with SMTP id 57so144695wri
	for <pgsql-sql@postgresql.org>; Thu, 17 Aug 2006 11:22:03 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=gnXvCdolhGzd74r4NKs2rFyBoaOPSh00+ey68xp8v7rkVRqoSPmPFBIxTs4EZR0NdtWvxht315bEX5eU70N0pEISI4zsm/v4MTXq05g/wHMAQ4Y98cUr7b0h2EopnWwJC5vld2bSJk0qUNua+J0kQP5Yquod5pPlK1/0J+25AZQ=
Received: by 10.66.222.9 with SMTP id u9mr1353106ugg;
	Thu, 17 Aug 2006 11:22:03 -0700 (PDT)
Received: by 10.66.248.13 with HTTP; Thu, 17 Aug 2006 11:22:03 -0700 (PDT)
Message-ID: <7539aebb0608171122n771f4862p4e2482aa2093b56b@mail.gmail.com>
Date: Thu, 17 Aug 2006 14:22:03 -0400
From: Sumeet <asumeet@gmail.com>
To: "Julian Scarfe" <julian@avbrief.com>
Subject: Re: NULL becomes default
Cc: pgsql-sql@postgresql.org
In-Reply-To: <029c01c6c228$8e8e5210$0600a8c0@Wilbur>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_29254_1833761.1155838923124"
References: <029c01c6c228$8e8e5210$0600a8c0@Wilbur>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.696 tagged_above=0 required=5
	tests=DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200608/135
X-Sequence-Number: 26093

------=_Part_29254_1833761.1155838923124
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Julian u need to reset your sequence, i had run through the same problem.


On 8/17/06, Julian Scarfe <julian@avbrief.com> wrote:
>
> A surrogate key has been introduced on a table with modifiers:
>
>    Column    |            Type             |
> Modifiers
>
> -------------+-----------------------------+------------------------------------------------------------
> new_id    | integer                     | not null default
> nextval(('some_id_seq'::text)::regclass)
>
> However, some legacy software inserts into the table by first getting
> column
> names and then composing an INSERT for all the columns, filling in any
> undefined values with NULLs.
>
> Thus when it attempts to insert a row I get a:
>
> ERROR:  null value in column "new_id" violates not-null constraint



To fix this, you need to do something like:

SELECT setval('public.fdata _fid_seq', max(fid)+1) FROM fdata;
This will make sure that the next *value* your *sequence* generates is
greater than any key that already exists in the table.

>> taken from tom lane.

-- 
Thanks,
Sumeet.

------=_Part_29254_1833761.1155838923124
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Julian u need to reset your sequence, i had run through the same problem. <br><br><div><span class="gmail_quote">On 8/17/06, <b class="gmail_sendername">Julian Scarfe</b> &lt;<a href="mailto:julian@avbrief.com">julian@avbrief.com
</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">A surrogate key has been introduced on a table with modifiers:<br><br>
&nbsp;&nbsp; Column&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>Modifiers<br>-------------+-----------------------------+------------------------------------------------------------<br> new_id&nbsp;&nbsp;&nbsp;&nbsp;| integer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null default
<br>nextval(('some_id_seq'::text)::regclass)<br><br>However, some legacy software inserts into the table by first getting column<br>names and then composing an INSERT for all the columns, filling in any<br>undefined values with NULLs.
<br><br>Thus when it attempts to insert a row I get a:<br><br>ERROR:&nbsp;&nbsp;null value in column &quot;new_id&quot; violates not-null constraint</blockquote><div><br>
<br>
<p>To fix this, you need to do something like: <br> </p>
<p>SELECT setval('public.fdata _fid_seq', max(fid)+1) FROM fdata;  </p>
This will make sure that the next <b style="color: black; background-color: rgb(255, 102, 255);">value</b> your <b style="color: black; background-color: rgb(255, 153, 153);">sequence</b> generates is <br>
 greater than any key that already exists in the table. <br>
<br>
</div></div>&gt;&gt; taken from tom lane.<br><br>-- <br>Thanks,<br>Sumeet.

------=_Part_29254_1833761.1155838923124--