Postgres Pro
Downloads
Home   >   mailing lists

Thread: Serial

Serial

From
"Shavonne Marietta Wijesinghe"
Date:
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 ??
 
Thanks alot.
 
 
Shavonne Wijesinghe

Re: Serial

From
"A. Kretschmer"
Date:
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

Re: Serial

From
"Shavonne Marietta Wijesinghe"
Date:
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

Re: Serial

From
"Milen A. Radev"
Date:
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

Re: Serial

From
"A. Kretschmer"
Date:
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

Re: Serial

From
"Shavonne Marietta Wijesinghe"
Date:
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

Re: Serial

From
"A. Kretschmer"
Date:
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

Re: Serial

From
Scott Marlowe
Date:
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?

Re: Serial

From
Richard Broersma Jr
Date:
--- > 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.