pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSerial
9+ messages / 5 participants
[nested] [flat]
* Serial
@ 2007-04-03 09:20 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 2 replies; 9+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-04-03 09:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 09:32 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 1 reply; 9+ messages in thread
From: A. Kretschmer @ 2007-04-03 09:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 09:55 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 9+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-04-03 09:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 10:10 Milen A. Radev <milen@radev.net>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 0 replies; 9+ messages in thread
From: Milen A. Radev @ 2007-04-03 10:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 10:14 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 1 reply; 9+ messages in thread
From: A. Kretschmer @ 2007-04-03 10:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 10:29 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-04-03 10:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 10:40 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 9+ messages in thread
From: A. Kretschmer @ 2007-04-03 10:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 14:17 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-04-03 14:17 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Serial
@ 2007-04-03 15:24 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 9+ messages in thread
From: Richard Broersma Jr @ 2007-04-03 15:24 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
--- > 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-04-03 15:24 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-03 09:20 Serial Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-04-03 09:32 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-03 09:55 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-04-03 10:10 ` Milen A. Radev <milen@radev.net>
2007-04-03 10:14 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-03 10:29 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-04-03 10:40 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-03 14:17 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-03 15:24 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox