From jlebrun@cplus.org Fri Feb  25 10:02:46 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 11FB61337B61
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 25 Feb 2011 06:02:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 70090-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 25 Feb 2011 10:02:38 +0000 (UTC)
X-Greylist: delayed 12:53:17.533219 by SQLgrey-1.7.6
Received: from toroondcbmts06-srv.bellnexxia.net
	(toroondcbmts06-srv.bellnexxia.net [207.236.237.40])
	by mail.postgresql.org (Postfix) with ESMTP id F1D921337B4F
	for <pgsql-sql@postgresql.org>; Fri, 25 Feb 2011 06:02:37 -0400 (AST)
Received: from toip58-bus.srvr.bell.ca ([67.69.240.185])
	by toroondcbmts06-srv.bellnexxia.net
	(InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id
	<20110224210918.KOJU27160.toroondcbmts06-srv.bellnexxia.net@toip58-bus.srvr.bell.ca>
	for <pgsql-sql@postgresql.org>; Thu, 24 Feb 2011 16:09:18 -0500
Received: from toip35-bus.srvr.bell.ca ([67.69.240.36])
	by toip58-bus.srvr.bell.ca with ESMTP; 24 Feb 2011 16:09:06 -0500
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: 
 AlYHAL5QZk1Fn/tO/2dsb2JhbACJcooCA4Qljwi+Hg2DC4JIBIUQhww
Received: from shbkpq40-1168112462.sdsl.bell.ca (HELO vserver)
	([69.159.251.78])
	by toip35-bus.srvr.bell.ca with SMTP; 24 Feb 2011 16:09:05 -0500
Message-ID: <7AD8C28EFB264E22BB784F83949DF0D2@Cplus.int>
From: "Jacques Lebrun" <jlebrun@cplus.org>
To: <pgsql-sql@postgresql.org>
Subject: Problem with serial counters
Date: Thu, 24 Feb 2011 16:09:04 -0500
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000D_01CBD43D.280BB2E0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-Antivirus: avast! (VPS 110224-0, 24/02/2011), Outbound message
X-Antivirus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201102/121
X-Sequence-Number: 35197

This is a multi-part message in MIME format.

------=_NextPart_000_000D_01CBD43D.280BB2E0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

When I install my application, I use a SQL script to create the =
different tables.
Most of my tables start with a Serial column called RowId.
In my setup script I insert a few records into some tables, providing =
all fields, including the RowId (1, 2, 3, etc).

The problem I have is that when my user tries to insert a new record =
without the RowId field,
Postgres complains that it finds a duplicate key. =20
I guess when I do an insert with all the fields (including the RowId), =
Postgres does not increment the serial counter.

I cannot remove the RowId field from the Insert of my setup script =
because this setup script is also used by customers using MySQL.

What can I do to force PostGres to update the internat serial counter =
when I do an insert with a specified value for the serial?



Jacques Lebrun
Web Mobile Inc
819-563-0133
JLebrun@cplus.org
------=_NextPart_000_000D_01CBD43D.280BB2E0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.19019">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2 face=3DArial>When I install my application, I use a =
SQL script=20
to create the different tables.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>Most of my tables start with a Serial =
column called=20
RowId.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>In my setup script I insert a few =
records into some=20
tables, providing all fields, including the RowId (1, 2, 3, =
etc).</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>The problem I have is that when my user =
tries to=20
insert a new record without the RowId field,</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>Postgres complains that it finds a =
duplicate=20
key.&nbsp; </FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>I guess when I do an insert with all =
the fields=20
(including the RowId), Postgres does not increment the serial=20
counter.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>I cannot remove the RowId field from =
the Insert of=20
my setup script because this setup script is also used by customers =
using=20
MySQL.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>What can I do to force PostGres to =
update the=20
internat serial counter when I do an insert with a specified value for =
the=20
serial?</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>Jacques Lebrun<BR>Web Mobile=20
Inc<BR>819-563-0133<BR><A=20
href=3D"mailto:JLebrun@cplus.org">JLebrun@cplus.org</A></FONT></DIV></BOD=
Y></HTML>

------=_NextPart_000_000D_01CBD43D.280BB2E0--




From vibhor.aim@gmail.com Fri Feb  25 18:47:11 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 346221337BBB
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 25 Feb 2011 14:47:11 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 69532-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 25 Feb 2011 18:47:03 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com
	[209.85.213.46])
	by mail.postgresql.org (Postfix) with ESMTP id 177301337BBA
	for <pgsql-sql@postgresql.org>; Fri, 25 Feb 2011 14:47:02 -0400 (AST)
Received: by ywj3 with SMTP id 3so551450ywj.19
	for <pgsql-sql@postgresql.org>; Fri, 25 Feb 2011 10:47:02 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:subject:mime-version:content-type:from
	:x-priority:in-reply-to:date:cc:content-transfer-encoding:message-id
	:references:to:x-mailer;
	bh=aF+VRB8H4Zs1p8CyvnvVsAlS3X0pPMRzuThVKCLJZlU=;
	b=ax1PM2Rx0TTuEdudUloh8Jed2N/xRWtSN0kzTwejfnLbIsAom6A9Dzb5CI0Qcxq9as
	qd++OwHUhuhndNf9KdU6XcH7WkNgvOPQp3hiOwdZ/WxQGpyOu/9zn7Xc2Dow99iAH8Sz
	l54AmFXD7NkWrnFENokFAYZx2iHBlJqYpePc4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:x-priority:in-reply-to:date
	:cc:content-transfer-encoding:message-id:references:to:x-mailer;
	b=s3P9jSaR5MXCtEZT33EPwPUI+zYRtaZID8jywcVpHaFE8Wfl/tCPFGU5pYQLVkafcA
	gwIixjaPrA3BpaY1KcskI9zrs4Wfmoc0K/SKteJgIi/cqZ8H33RI41CDl9GLVEO9Xo4r
	HIJ0shT3q+Vr95ddKKTi9MZ/nRHmxSmU5Jjl8=
Received: by 10.100.92.14 with SMTP id p14mr1177495anb.58.1298659622160;
	Fri, 25 Feb 2011 10:47:02 -0800 (PST)
Received: from [172.24.35.132] ([220.225.109.93])
	by mx.google.com with ESMTPS id x31sm1172099ana.9.2011.02.25.10.46.59
	(version=TLSv1/SSLv3 cipher=OTHER);
	Fri, 25 Feb 2011 10:47:01 -0800 (PST)
Subject: Re: Problem with serial counters
Mime-Version: 1.0 (Apple Message framework v1082)
Content-Type: text/plain; charset=us-ascii
From: Vibhor Kumar <vibhor.aim@gmail.com>
X-Priority: 3
In-Reply-To: <7AD8C28EFB264E22BB784F83949DF0D2@Cplus.int>
Date: Sat, 26 Feb 2011 00:17:24 +0530
Cc: <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <E58C1ADD-3B7D-470B-8061-A147387D89B9@gmail.com>
References: <7AD8C28EFB264E22BB784F83949DF0D2@Cplus.int>
To: Jacques Lebrun <jlebrun@cplus.org>
X-Mailer: Apple Mail (2.1082)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201102/125
X-Sequence-Number: 35201


On Feb 25, 2011, at 2:39 AM, Jacques Lebrun wrote:
> What can I do to force PostGres to update the internat serial counter =
when I do an insert with a specified value for the serial?
> =20

After inserting the all the Data, use ALTER SEQUENCE Command:
http://www.postgresql.org/docs/8.4/static/sql-altersequence.html

Thanks & Regards,
Vibhor




From dev@archonet.com Tue Mar  1 20:29:54 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id CE6661337B34
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  1 Mar 2011 16:29:54 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 99511-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  1 Mar 2011 20:29:47 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk
	[84.51.143.99])
	by mail.postgresql.org (Postfix) with ESMTP id 4119A1336971
	for <pgsql-sql@postgresql.org>; Tue,  1 Mar 2011 16:29:46 -0400 (AST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by mail.archonet.com (Postfix) with ESMTP id 8AA107C140;
	Tue,  1 Mar 2011 20:29:43 +0000 (GMT)
Message-ID: <4D6D5737.3030208@archonet.com>
Date: Tue, 01 Mar 2011 20:29:43 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.15pre) Gecko/20110202 Lightning/1.0b2 Shredder/3.1.9pre
MIME-Version: 1.0
To: Jacques Lebrun <jlebrun@cplus.org>
CC: pgsql-sql@postgresql.org
Subject: Re: Problem with serial counters
References: <7AD8C28EFB264E22BB784F83949DF0D2@Cplus.int>
In-Reply-To: <7AD8C28EFB264E22BB784F83949DF0D2@Cplus.int>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.918 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RDNS_DYNAMIC=0.982
X-Spam-Level: 
X-Archive-Number: 201103/4
X-Sequence-Number: 35206

On 24/02/11 21:09, Jacques Lebrun wrote:
> I guess when I do an insert with all the fields (including the RowId),
> Postgres does not increment the serial counter.
> I cannot remove the RowId field from the Insert of my setup script
> because this setup script is also used by customers using MySQL.

Apart from Vibhor's suggestion (which is the typical way PG does it) you 
can also set the auto field to DEFAULT:

INSERT INTO mytable (rowid, other) VALUES (DEFAULT,'a'), (DEFAULT,'b');

This should work on either system.

-- 
   Richard Huxton
   Archonet Ltd