Обсуждение: if exists...does it exists for insert statments?

could i use a sentence similar to the mysql sentence

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

in postgresql?

pau


--
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

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

> in postgresql?
>
> pau
>
>
> --
> 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:
> 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




--
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
worksin PostgreSQL versions >= 8.2. 

Regards,
Richard Broersma Jr.