pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[Re: PRIMARY KEY]
3+ messages / 3 participants
[nested] [flat]
* [Re: PRIMARY KEY]
@ 2007-03-07 12:20 Phillip Smith <fukawi2@iinet.net.au>
0 siblings, 1 reply; 3+ messages in thread
From: Phillip Smith @ 2007-03-07 12:20 UTC (permalink / raw)
To: pgsql-sql
If you actually need to know the value of N_GEN in your ASP application,
you will need to query the database first and select the NEXTVAL from
the sequence that the "serial" data type will create, then use that
returned value in your insert - ie, DON'T exclude it from the insert,
otherwise it will default to NEXTVAL again and return a different value.
The only catch with this is that you most likely won't end up with
contiguous values in this column - ie, if a user cancels after you seect
nextval, but before you insert - the value of the sequence has already
increased, and will be increased again before returning a value when you
next select nextval
Cheers,
~p
On Wed, 2007-03-07 at 12:57 +0100, M.P.Dankoor wrote:
> Hello,
>
> Is it possible to redesign your table as follows:
>
> create table Mod48_00_2007 (
> ID text,
> N_GEN serial not null,
> FORMSTORE text,
> COD_NOTATIO text,
> PA_COGNOME text,
> constraint pk_Mod48_00_2007 primary key (N_GEN)
> );
>
> Your insert simply becomes:
> INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME)
> VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS')
>
> Do note that you do not refer to the N_GEN column, it will use the
> next value, please refer to
> http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
> for more information.
>
> Mario
>
> Shavonne Marietta Wijesinghe wrote:
>
> > Hello
> >
> > I have created a table
> > CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY,
> > FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
> >
> > And i insert the rows via a form in ASP. When the form loads i have
> > a functin that goes and gets the value of the field N_GEN adds 1 to
> > it and shows it to the user.
> > The problem is when i have 2 users working at the same time.
> >
> > For example the last value in my field N_GEN is 2
> > When both the users A and B loads the form (ASP page) it sees N_GEN
> > = 3 :)
> >
> > So they fill in the form and user A clicks on the button OK and the
> > record has been inserted with N_GEN = 3. But when the user B clicks
> > on the button the record is not inserted because it has the same key
> > "3"
> >
> > INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO,
> > PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE
> > MARTINIS')
> >
> > Is there any way i can do this automatically? i mean maybe i have to
> > use someother property instead of "Primary Key" ??
> >
> > Thanks
> >
> > Shavonne Wijesinghe
> >
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [Re: PRIMARY KEY]
@ 2007-03-09 15:03 Bruno Wolff III <bruno@wolff.to>
parent: Phillip Smith <fukawi2@iinet.net.au>
0 siblings, 1 reply; 3+ messages in thread
From: Bruno Wolff III @ 2007-03-09 15:03 UTC (permalink / raw)
To: Phillip Smith <fukawi2@iinet.net.au>; +Cc: pgsql-sql
On Wed, Mar 07, 2007 at 23:20:12 +1100,
Phillip Smith <fukawi2@iinet.net.au> wrote:
> If you actually need to know the value of N_GEN in your ASP application,
> you will need to query the database first and select the NEXTVAL from
> the sequence that the "serial" data type will create, then use that
> returned value in your insert - ie, DON'T exclude it from the insert,
> otherwise it will default to NEXTVAL again and return a different value.
In 8.2, you can also use the RETURNING clause to get those values.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [Re: PRIMARY KEY]
@ 2007-03-09 22:44 Phillip Smith <phillip.smith@weatherbeeta.com.au>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 3+ messages in thread
From: Phillip Smith @ 2007-03-09 22:44 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Of course - my bad... That's the main reason I upgraded to 8.2!
On Fri, 2007-03-09 at 09:03 -0600, Bruno Wolff III wrote:
> On Wed, Mar 07, 2007 at 23:20:12 +1100,
> Phillip Smith <fukawi2@iinet.net.au> wrote:
> > If you actually need to know the value of N_GEN in your ASP application,
> > you will need to query the database first and select the NEXTVAL from
> > the sequence that the "serial" data type will create, then use that
> > returned value in your insert - ie, DON'T exclude it from the insert,
> > otherwise it will default to NEXTVAL again and return a different value.
>
> In 8.2, you can also use the RETURNING clause to get those values.
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-03-09 22:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-07 12:20 [Re: PRIMARY KEY] Phillip Smith <fukawi2@iinet.net.au>
2007-03-09 15:03 ` Bruno Wolff III <bruno@wolff.to>
2007-03-09 22:44 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
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