From: Richard Huxton <dev@archonet.com>
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
Cc: Jean-David Beyer <jeandavid8@verizon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
Date: Tue, 12 Feb 2008 12:01:04 +0000
Message-ID: <47B18A80.4030802@archonet.com> (raw)
In-Reply-To: <00fa01c86d67$47e04bc0$3102a8c0@dream>
References: <010901c86ca6$3b66e870$3102a8c0@dream>
<873arzscnu.fsf@oxford.xeocode.com>
<024301c86cca$591c9c60$3102a8c0@dream>
<47B07DFA.8030003@verizon.net>
<00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
<47B1767F.3030802@archonet.com>
<00fa01c86d67$47e04bc0$3102a8c0@dream>
Shavonne Marietta Wijesinghe wrote:
> Even though n_gen is defined as a serial I can't let it handle the
> progressive key by its self since there is the need that some records
> should have the same value.
Of course you can - the default is only provided if you don't provide
your own.
> That's why i use 3 primary keys.
No, you're not. By definition you can only have one primary key. You are
using a 3-column primary key.
> A | B | C
> ---+---+---
> 1 | 1 | 1
> 2 | 1 | 3
> 2 | 2 | 3
> 2 | 3 | 3
> 3 | 1 | 2
> 3 | 2 | 2
> 4 | 1 | 1
>
> The 3 keys A, B, C are defined as Serial and Primay Keys
You don't have 3 keys, you have 3 columns and one primary key (A,B,C).
I'm not clear why B and C are serial - I don't see what that gets you.
> Anyway the other suggestion, Blocking the second user from reading the
> db. So for the second user I could give A temp key something like 0 and
> then do a select before the submit and change the value.
>
> But how is it possible to know if some other user is reading the db??
Don't try. If you want to insert some rows with a new value for A do
something like:
INSERT INTO my_shevi (a,b,c) VALUES (DEFAULT, 1, 1);
or
INSERT INTO my_shevi (a,b,c) VALUES (nextval(<sequence-name-here>, 1, 1);
INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 2);
INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 3);
etc.
Relevant parts of the manual (in 8.3 anyway):
9.15. Sequence Manipulation Functions
9.22. System Information Functions
The second chapter is for pg_get_serial_sequence() which might be useful
if you need to do this a lot.
Oh - and please try trimming unwanted parts of the message when you
reply. There was 100 unnecessary lines below here.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, shavonne.marietta@studioform.it, jeandavid8@verizon.net
Subject: Re: Check before INSERT INTO
In-Reply-To: <47B18A80.4030802@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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