pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPRIMARY KEY
5+ messages / 3 participants
[nested] [flat]
* PRIMARY KEY
@ 2007-03-07 11:35 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-03-07 11:51 ` Re: PRIMARY KEY Richard Huxton <dev@archonet.com>
2007-03-07 11:57 ` Re: PRIMARY KEY M.P.Dankoor <m.p.dankoor@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-03-07 11:35 UTC (permalink / raw)
To: pgsql-sql
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] 5+ messages in thread
* Re: PRIMARY KEY
2007-03-07 11:35 PRIMARY KEY Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2007-03-07 11:51 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2007-03-07 11:51 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
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.
Check the manuals for "sequences" and "serial type".
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PRIMARY KEY
2007-03-07 11:35 PRIMARY KEY Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2007-03-07 11:57 ` M.P.Dankoor <m.p.dankoor@gmail.com>
2007-03-07 12:50 ` Re: PRIMARY KEY M.P.Dankoor <m.p.dankoor@gmail.com>
2007-03-07 13:16 ` Re: PRIMARY KEY Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 2 replies; 5+ messages in thread
From: M.P.Dankoor @ 2007-03-07 11:57 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
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] 5+ messages in thread
* Re: PRIMARY KEY
2007-03-07 11:35 PRIMARY KEY Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-03-07 11:57 ` Re: PRIMARY KEY M.P.Dankoor <m.p.dankoor@gmail.com>
@ 2007-03-07 12:50 ` M.P.Dankoor <m.p.dankoor@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: M.P.Dankoor @ 2007-03-07 12:50 UTC (permalink / raw)
To: pgsql-sql
Phillip Smith 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.
>
> 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
>
That's precisely why I would not use the nextval call, you might end up
with gaps. Moreover you are making 2 database calls instead of one.
But then again, if your application needs to know the value you can't
really avoid using nextval (good thing it's concurrency proof)
Mario
Phillip Smith 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.
>
> 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
>>>
>>
>
> ********************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] 5+ messages in thread
* Re: PRIMARY KEY
2007-03-07 11:35 PRIMARY KEY Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-03-07 11:57 ` Re: PRIMARY KEY M.P.Dankoor <m.p.dankoor@gmail.com>
@ 2007-03-07 13:16 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 0 replies; 5+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-03-07 13:16 UTC (permalink / raw)
To: pgsql-sql
Thanks alot Mario. It did exactly what i wanted. I shall also take alook at the link you gave for more details..
Shavonne Wijesinghe
----- Original Message -----
From: M.P.Dankoor
To: Shavonne Marietta Wijesinghe
Cc: pgsql-sql@postgresql.org
Sent: Wednesday, March 07, 2007 12:57 PM
Subject: Re: [SQL] PRIMARY KEY
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] 5+ messages in thread
end of thread, other threads:[~2007-03-07 13:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-07 11:35 PRIMARY KEY Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-03-07 11:51 ` Richard Huxton <dev@archonet.com>
2007-03-07 11:57 ` M.P.Dankoor <m.p.dankoor@gmail.com>
2007-03-07 12:50 ` M.P.Dankoor <m.p.dankoor@gmail.com>
2007-03-07 13:16 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
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