Обсуждение: 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
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
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 worksin PostgreSQL versions >= 8.2. Regards, Richard Broersma Jr.