if exists...does it exists for insert statments?

Hello

On 11/12/2007, Pau Marc Munoz Torres <paumarc@gmail.com> wrote:

could i use a sentence similar to the mysql sentence

insert if not exist into SP values ('cesp','sp');

insert into sp
select 'cesp','sp'
except
select * from SP;

Regards
Pavel Stehule

--- On Tue, 12/11/07, Pau Marc Munoz Torres <paumarc@gmail.com> wrote:

could i use a sentence similar to the mysql sentence

insert if not exist into SP values
('cesp','sp');

in postgresql?

Using standard ANSI-SQL the statement could be re-written:

INSERT INTO SP
SELECT a, b
FROM VALUES ( 'cesp', 'sp' ) AS tmp( a, b )
LEFT JOIN Sp
ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;

Regards,
Richard Broersma Jr.

Thanks, i'll test it tomorrow

pau

2007/12/11, Richard Broersma Jr <rabroersma@yahoo.com>:

--- On Tue, 12/11/07, Pau Marc Munoz Torres <paumarc@gmail.com> wrote:

could i use a sentence similar to the mysql sentence

insert if not exist into SP values
('cesp','sp');

in postgresql?

Using standard ANSI-SQL the statement could be re-written:

INSERT INTO SP
SELECT a, b
FROM VALUES ( 'cesp', 'sp' ) AS tmp( a, b )
LEFT JOIN Sp
ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;

Regards,
Richard Broersma Jr.

--
Pau Marc Muñoz Torres

Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : paumarc.munoz@bioinf.uab.cat

--- On Tue, 12/11/07, Pau Marc Munoz Torres <paumarc@gmail.com> wrote:

Thanks, i'll test it tomorrow

OOPS, I just noticed a mistake.

INSERT INTO SP
SELECT a, b
FROM ( VALUES ( 'cesp', 'sp' )) AS tmp( a, b )
LEFT JOIN Sp
ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;

I forgot the parentheses that the FROM clause requires when using the VALUES predicate. Also remember that this only works in PostgreSQL versions >= 8.2.

Regards,
Richard Broersma Jr.