Message-ID: <47B18A80.4030802@archonet.com>
Date: Tue, 12 Feb 2008 12:01:04 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
CC: Jean-David Beyer <jeandavid8@verizon.net>, pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
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>
In-Reply-To: <00fa01c86d67$47e04bc0$3102a8c0@dream>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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