From shavonne.marietta@studioform.it Tue Apr  3 09:21:17 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 041FE9FB9E1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 06:21:17 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 57859-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 06:21:09 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from smtpout09.attiva.biz (smtpout09.attiva.biz [85.37.16.7])
	by postgresql.org (Postfix) with ESMTP id DCD759FB9D1
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 06:21:08 -0300 (ADT)
Received: from FBCMFE02B12.fbc.local ([192.168.30.89]) by smtpout09.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 3 Apr 2007 11:21:06 +0200
Received: from dream ([88.34.161.130]) by FBCMFE02B12.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Tue, 3 Apr 2007 11:19:52 +0200
Message-ID: <008301c775d1$4b224420$1102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: <pgsql-sql@postgresql.org>
Subject: Serial
Date: Tue, 3 Apr 2007 11:20:16 +0200
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0080_01C775E2.0E20AE70"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3028
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-OriginalArrivalTime: 03 Apr 2007 09:19:52.0321 (UTC)
	FILETIME=[3C466710:01C775D1]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.119 tagged_above=0 required=5 tests=AWL=0.303,
	BAYES_40=-0.185, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200704/18
X-Sequence-Number: 28018

This is a multi-part message in MIME format.

------=_NextPart_000_0080_01C775E2.0E20AE70
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Ok so i'm posting alot in the forums. Anyway for a change i have another =
problem ^___^

I have a table that has a field n_gen serial NOT NULL

ermm let me explain. I have 5 records inserted (n_gen =3D 1, 2, 3, 4, 5) =


At a surtain point i DELETE the record 2 and UPDATE the record 5 with =
the n_gen 2. So now i have 4 records (n_gen =3D 1, 2 (EX 5), 3, 4)  Upto =
this point i'm ok.=20

The problem is when i INSERT a new record. It takes the value n_gen =3D =
6 but i need it to take the value 5. It keeps a record of the delete =
records.

Is there anyway in PostgreSQL i can change it? or another progressive =
that i can use for n_gen ??

Thanks alot.


Shavonne Wijesinghe

------=_NextPart_000_0080_01C775E2.0E20AE70
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 http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16414" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Ok so i'm posting alot in the forums. =
Anyway for a=20
change i have another problem ^___^</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have a table that has a field <FONT=20
face=3D"Courier New" color=3D#ff0000><STRONG>n_gen serial NOT=20
NULL</STRONG></FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>ermm let me explain. I have 5 records =
inserted=20
(n_gen =3D 1, 2, 3, 4, 5) </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>At a surtain point i DELETE the record =
2 and UPDATE=20
the record 5 with the n_gen 2. So now i have 4 records (n_gen =3D 1, 2 =
(EX 5), 3,=20
4)&nbsp; Upto this point i'm ok. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The problem is when i INSERT a new =
record. It takes=20
the value n_gen =3D 6&nbsp;but i need it to take the value 5. It keeps a =
record of=20
the delete records.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Is there anyway in PostgreSQL i can =
change it? or=20
another progressive that i can use for n_gen ??</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks alot.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Shavonne =
Wijesinghe<BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_0080_01C775E2.0E20AE70--




From andreas.kretschmer@schollglas.com Tue Apr  3 09:32:29 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 850C59FB881
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 06:32:29 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 82680-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 06:32:21 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.174])
	by postgresql.org (Postfix) with ESMTP id 0560F9FB688
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 06:32:24 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu2) with ESMTP (Nemesis),
	id 0MKwtQ-1HYfNH0MBm-0004nC; Tue, 03 Apr 2007 11:32:23 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1HYfNG-0001I1-00
	for pgsql-sql@postgresql.org; Tue, 03 Apr 2007 11:32:22 +0200
Date: Tue, 3 Apr 2007 11:32:22 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Serial
Message-ID: <20070403093222.GB30108@a-kretschmer.de>
References: <008301c775d1$4b224420$1102a8c0@dream>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <008301c775d1$4b224420$1102a8c0@dream>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX1/x2pgGOfHq6nJe305r6xYUrF9vruedX5f8VKt
	jLyu5nvtXS2r6k8c60keJ1eoL+XMLGT75yrzUC4+N9CDw7r77i
	6ykeOWANsmIJI12kzGx3tZnpUfScn8t
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/19
X-Sequence-Number: 28019

am  Tue, dem 03.04.2007, um 11:20:16 +0200 mailte Shavonne Marietta Wijesinghe folgendes:
> The problem is when i INSERT a new record. It takes the value n_gen = 6 but i
> need it to take the value 5. It keeps a record of the delete records.
>  
> Is there anyway in PostgreSQL i can change it? or another progressive that i
> can use for n_gen ??

Yes, setval().
http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net



From shavonne.marietta@studioform.it Tue Apr  3 10:01:00 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 07AC29FB652
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:01:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 01826-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:00:51 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from smtpout25.fbc.local (smtpout25.attiva.biz [85.37.16.26])
	by postgresql.org (Postfix) with ESMTP id 144C29FB644
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 07:00:54 -0300 (ADT)
Received: from FBCMFE02B15.fbc.local ([192.168.30.161]) by smtpout25.fbc.local
	with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 3 Apr 2007 12:00:52 +0200
Received: from dream ([88.34.161.130]) by FBCMFE02B15.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Tue, 3 Apr 2007 11:54:49 +0200
Message-ID: <00b101c775d6$2d313840$1102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: <pgsql-sql@postgresql.org>
References: <008301c775d1$4b224420$1102a8c0@dream>
	<20070403093222.GB30108@a-kretschmer.de>
Subject: Re: Serial
Date: Tue, 3 Apr 2007 11:55:10 +0200
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3028
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-OriginalArrivalTime: 03 Apr 2007 09:54:50.0332 (UTC)
	FILETIME=[1EC979C0:01C775D6]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/20
X-Sequence-Number: 28020

thanks. I read the page you gave.

CREATE SEQUENCE seq_mytable_n_gen;

CREATE TABLE mytable
(
  n_gen int nextval('seq_mytable_n_gen'),
  mycolumn1 int,
  mycolumn2 int
);


i tried creating it like that. The sequence was created without any error. 
But for the create table i get

ERROR:  syntax error at or near "nextval" at character 38

What should i do?


Shavonne Wijesinghe

----- Original Message ----- 
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, April 03, 2007 11:32 AM
Subject: Re: [SQL] Serial


> am  Tue, dem 03.04.2007, um 11:20:16 +0200 mailte Shavonne Marietta 
> Wijesinghe folgendes:
>> The problem is when i INSERT a new record. It takes the value n_gen = 6 
>> but i
>> need it to take the value 5. It keeps a record of the delete records.
>>
>> Is there anyway in PostgreSQL i can change it? or another progressive 
>> that i
>> can use for n_gen ??
>
> Yes, setval().
> http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
>
>
> Andreas
> -- 
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate 




From milen@radev.net Tue Apr  3 10:11:33 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id A26D49FB794
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:11:33 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 32727-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:11:29 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by postgresql.org (Postfix) with ESMTP id 6A65B9FB6B6
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 07:11:29 -0300 (ADT)
Received: from list by ciao.gmane.org with local (Exim 4.43)
	id 1HYfyn-0008F9-5S
	for pgsql-sql@postgresql.org; Tue, 03 Apr 2007 12:11:09 +0200
Received: from pirus.securax.be ([213.16.46.130])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 03 Apr 2007 12:11:09 +0200
Received: from milen by pirus.securax.be with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 03 Apr 2007 12:11:09 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: "Milen A. Radev" <milen@radev.net>
Subject: Re: Serial
Date: Tue, 03 Apr 2007 13:10:58 +0300
Lines: 34
Message-ID: <eut97i$n5f$1@sea.gmane.org>
References: <008301c775d1$4b224420$1102a8c0@dream>
	<20070403093222.GB30108@a-kretschmer.de>
	<00b101c775d6$2d313840$1102a8c0@dream>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@sea.gmane.org
X-Gmane-NNTP-Posting-Host: pirus.securax.be
User-Agent: Thunderbird 2.0b2 (Windows/20070116)
In-Reply-To: <00b101c775d6$2d313840$1102a8c0@dream>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/21
X-Sequence-Number: 28021

Shavonne Marietta Wijesinghe wrote:
> thanks. I read the page you gave.
> 
> CREATE SEQUENCE seq_mytable_n_gen;
> 
> CREATE TABLE mytable
> (
>  n_gen int nextval('seq_mytable_n_gen'),
>  mycolumn1 int,
>  mycolumn2 int
> );
> 
> 
> i tried creating it like that. The sequence was created without any
> error. But for the create table i get
> 
> ERROR:  syntax error at or near "nextval" at character 38
> 
> What should i do?


Add the missing "default":

CREATE TABLE mytable
(
  n_gen int DEFAULT nextval('seq_mytable_n_gen'),
  mycolumn1 int,
  mycolumn2 int
);



-- 
Milen A. Radev




From andreas.kretschmer@schollglas.com Tue Apr  3 10:14:53 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id CA6B79FB794
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:14:53 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 64404-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:14:46 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.188])
	by postgresql.org (Postfix) with ESMTP id CD8179FB70A
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 07:14:49 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu6) with ESMTP (Nemesis),
	id 0ML29c-1HYg2J29PK-0003W0; Tue, 03 Apr 2007 12:14:48 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1HYg2I-0001Z0-00
	for pgsql-sql@postgresql.org; Tue, 03 Apr 2007 12:14:46 +0200
Date: Tue, 3 Apr 2007 12:14:46 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Serial
Message-ID: <20070403101446.GC30108@a-kretschmer.de>
References: <008301c775d1$4b224420$1102a8c0@dream>
	<20070403093222.GB30108@a-kretschmer.de>
	<00b101c775d6$2d313840$1102a8c0@dream>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <00b101c775d6$2d313840$1102a8c0@dream>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX1+hvJGyWw99sa+cTpIv56aAsp0R6SojKn20pPF
	kky2X+XHVAyhIwiSOjHR5nT0ybW/4tjGyN33Yg40ROY2ewYH6B
	7VOxKSdHtBcPDEiqNWzd6Mdys/QDE62
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/22
X-Sequence-Number: 28022

am  Tue, dem 03.04.2007, um 11:55:10 +0200 mailte Shavonne Marietta Wijesinghe folgendes:
> thanks. I read the page you gave.

Really?


> 
> CREATE SEQUENCE seq_mytable_n_gen;
> 
> CREATE TABLE mytable
> (
>  n_gen int nextval('seq_mytable_n_gen'),
>  mycolumn1 int,
>  mycolumn2 int
> );
> 
> 
> i tried creating it like that. The sequence was created without any error. 
> But for the create table i get
> 
> ERROR:  syntax error at or near "nextval" at character 38
> 
> What should i do?

Your question was, how to set the sequence to a new value, and my answer
was: use setval().

Now you tried to create a new table. You have a simple syntax error:

,----[  example  ]
| test=# create sequence testseq;
| CREATE SEQUENCE
| test=*# create table testtab (id int default nextval('testseq'));
| CREATE TABLE
`----

You forgot the word 'default'.



> 
> 
> Shavonne Wijesinghe
> 
> ----- Original Message ----- 
> From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>

Please, no silly text above with fullquote below, i read from top to
bottom...


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net



From shavonne.marietta@studioform.it Tue Apr  3 10:29:28 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 2FCB79FB843
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:29:28 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 89383-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:29:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from smtpout26.attiva.biz (smtpout26.attiva.biz [85.37.16.27])
	by postgresql.org (Postfix) with ESMTP id 553839FB84F
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 07:29:20 -0300 (ADT)
Received: from FBCMFE02B07.fbc.local ([192.168.30.95]) by smtpout26.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 3 Apr 2007 12:29:16 +0200
Received: from dream ([88.34.161.130]) by FBCMFE02B07.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Tue, 3 Apr 2007 12:29:15 +0200
Message-ID: <00e101c775da$fce4ec90$1102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: <pgsql-sql@postgresql.org>
References: <008301c775d1$4b224420$1102a8c0@dream>
	<20070403093222.GB30108@a-kretschmer.de>
	<00b101c775d6$2d313840$1102a8c0@dream>
	<20070403101446.GC30108@a-kretschmer.de>
Subject: Re: Serial
Date: Tue, 3 Apr 2007 12:29:37 +0200
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3028
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-OriginalArrivalTime: 03 Apr 2007 10:29:15.0985 (UTC)
	FILETIME=[EE031C10:01C775DA]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/23
X-Sequence-Number: 28023

I'm mixed up now. I was suppose to something but i did something else.

OK so i have my FAMOUS table with the n_gen serial NOT NULL
I got lost a bit. When and where do i use the setval() ??

For example i INSERT records via ASP. so i should put the setval() in the 
INSERT INTO of the ASP page??
And even when i do a SELECT i should use the setval()??

SELECT setval(N_GEN) FROM MyTable ORDER BY N_GEN::INT DESC
??

Shavonne Wijesinghe 




From andreas.kretschmer@schollglas.com Tue Apr  3 10:40:22 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id B63A29FB644
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:40:22 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 58551-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 07:40:19 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.183])
	by postgresql.org (Postfix) with ESMTP id 84C669FB2A0
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 07:40:18 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu0) with ESMTP (Nemesis),
	id 0MKwh2-1HYgQu4AKp-000415; Tue, 03 Apr 2007 12:40:16 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1HYgQu-0001nY-00
	for pgsql-sql@postgresql.org; Tue, 03 Apr 2007 12:40:12 +0200
Date: Tue, 3 Apr 2007 12:40:12 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Serial
Message-ID: <20070403104012.GD30108@a-kretschmer.de>
References: <008301c775d1$4b224420$1102a8c0@dream>
	<20070403093222.GB30108@a-kretschmer.de>
	<00b101c775d6$2d313840$1102a8c0@dream>
	<20070403101446.GC30108@a-kretschmer.de>
	<00e101c775da$fce4ec90$1102a8c0@dream>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <00e101c775da$fce4ec90$1102a8c0@dream>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX1+yat+5MO6zH8/UhplHwShdYd8klOdv8vSGbre
	RF6c1HCqWOAdYEr9dhD63owaPapaEFVD11TpYXVRHh8IxkmqZy
	/k/SbulppgfdxvzYZTS3SPtMJlrzJLc
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/24
X-Sequence-Number: 28024

am  Tue, dem 03.04.2007, um 12:29:37 +0200 mailte Shavonne Marietta Wijesinghe folgendes:
> I'm mixed up now. I was suppose to something but i did something else.
> 
> OK so i have my FAMOUS table with the n_gen serial NOT NULL
> I got lost a bit. When and where do i use the setval() ??

Only to manipulate the current value of this sequence.


> 
> For example i INSERT records via ASP. so i should put the setval() in the 
> INSERT INTO of the ASP page??

No. You can define your table like:

create table foo (id serial, ...)

This creates implicit a own sequence and set the default for id to
nextval().


> And even when i do a SELECT i should use the setval()??

For a select? No.

12:39 < akretschmer> ??sequence
12:39 < rtfm_please> For information about sequence
12:39 < rtfm_please> see http://techdocs.postgresql.org/college/001_sequences/index.php
12:39 < rtfm_please> or http://www.postgresql.org/docs/current/interactive/sql-createsequence.html
12:39 < rtfm_please> or http://www.postgresql.org/docs/current/static/functions-sequence.html


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net



From smarlowe@g2switchworks.com Tue Apr  3 14:17:40 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 8A5239FB77A
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 11:17:40 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 05171-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 11:17:24 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from exchange.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 3CE429FB733
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 11:17:25 -0300 (ADT)
Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com
	([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ;
	Tue,  3 Apr 2007 14:17:19 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	03 Apr 2007 09:17:19 -0500
Subject: Re: Serial
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <008301c775d1$4b224420$1102a8c0@dream>
References: <008301c775d1$4b224420$1102a8c0@dream>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1175609839.22459.3.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Tue, 03 Apr 2007 09:17:19 -0500
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/31
X-Sequence-Number: 28031

On Tue, 2007-04-03 at 04:20, Shavonne Marietta Wijesinghe wrote:
> Ok so i'm posting alot in the forums. Anyway for a change i have
> another problem ^___^
>  
> I have a table that has a field n_gen serial NOT NULL
>  
> ermm let me explain. I have 5 records inserted (n_gen = 1, 2, 3, 4, 5)
>  
> At a surtain point i DELETE the record 2 and UPDATE the record 5 with
> the n_gen 2. So now i have 4 records (n_gen = 1, 2 (EX 5), 3, 4)  Upto
> this point i'm ok. 
>  
> The problem is when i INSERT a new record. It takes the value n_gen =
> 6 but i need it to take the value 5. It keeps a record of the delete
> records.
>  
> Is there anyway in PostgreSQL i can change it? or another progressive
> that i can use for n_gen ??

Yes there is, and you generally shouldn't do it.  There are issues with
race conditions and misreferenced data that can happen when you try to
reuse sequence numbers.

Sadly, some poorly defined business processes require this.

Are you required to have sequential numbers, or is just something that
feels good / looks good?



From rabroersma@yahoo.com Tue Apr  3 15:27:00 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id D43E49FB9C8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 12:27:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 96838-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 12:26:54 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from web31803.mail.mud.yahoo.com (web31803.mail.mud.yahoo.com
	[68.142.207.66]) by postgresql.org (Postfix) with SMTP id 2B1DA9FBFFF
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 12:24:52 -0300 (ADT)
Received: (qmail 82631 invoked by uid 60001); 3 Apr 2007 15:24:42 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=1poZ+hXydznxsB0Dm5fSPlpZlpiCrwGKXDED/i/xe4XA35F1J7JNBsxgffQyn7SonpH6iJyYeRgazO505L/ySfJVaLQS+3rgIt3POpWvd7zwxsEY/p9JVMlvDJOKmhYYeazLAabnAqEfuZ/UkPkjg9qZ7F/J0M3U2aFgZKj3+oM=;
X-YMail-OSG: 
 jSvbERMVM1mdDvk7DNDbngoUyC4DkU5QlDJOOLk64vTRXyhM3NSPidM8dJ8wApNpLw35YxaxwGuVg6DdX1Qmw_BbthvGcqRmn.wSYMU1WWti0kqwDnA-
Received: from [63.203.180.122] by web31803.mail.mud.yahoo.com via HTTP;
	Tue, 03 Apr 2007 08:24:42 PDT
Date: Tue, 3 Apr 2007 08:24:42 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Serial
To: Scott Marlowe <smarlowe@g2switchworks.com>,
	Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1175609839.22459.3.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <407615.82627.qm@web31803.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.697 tagged_above=0 required=5 tests=AWL=-0.918,
	BAYES_20=-0.74, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708,
	DNS_FROM_RFC_WHOIS=1.447
X-Spam-Level: *
X-Archive-Number: 200704/34
X-Sequence-Number: 28034


--- > Yes there is, and you generally shouldn't do it.  There are issues with
> race conditions and misreferenced data that can happen when you try to
> reuse sequence numbers.
> 
> Sadly, some poorly defined business processes require this.
> 
> Are you required to have sequential numbers, or is just something that
> feels good / looks good?

The following link develops the use of a gapless sequence:
http://www.varlena.com/GeneralBits/130.php

Regards,
Richard Broersma Jr.