Re: Check before INSERT INTO - Mailing list pgsql-sql

From Richard Huxton
Subject Re: Check before INSERT INTO
Date
Msg-id 47B18A80.4030802@archonet.com
Whole thread Raw
In response to Re: Check before INSERT INTO  ("Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: "Shavonne Marietta Wijesinghe"
Date:
Subject: Re: Check before INSERT INTO
Next
From: Steve Midgley
Date:
Subject: Re: Usage of UUID with 8.3 (Windows)