select query help

[plork123]

Hi all

I'm trying to do a simple select in postgresql, something like this

if not exists (select 1 from table where a = 'blah')
begin
insert into table values ('a','b','c')

What i'm trying to do is to see whether a value already exists in a table, if it doens't i want to insert the value

But when i do this in query analyser if gives me an error on the if

Can anyone help me with this

Thanks

 

[NullTerminator]

You need to do this as a stored procedure.

 

[FRrogoy]

If you're using a language that allows you to handle the error you could just do the insert. If you get a duplicat key error you could handle it in whatever way is appropriate for tha application.

We usually do a select first (with the new key) and then check the number of fields returned. If 0, then we do the insert.

Frank

 

[ceco]

you have to write it as a function with plpgsql language for example

but the thing you tried is in a wrong direction, because if two oparations appear at the same time, they both will put that data

better, do it with unique constraint, and just insert the constraint will forbid two duplicate rows

the other way is with some kind of locking of the row with SELECT ... FOR UPDATE for example