Re: Insert only if not found

Yea i got it. I did the uppercase before i came to the select and it works
well. *Fingers crossed*

Thanks for all the help.

Ohh and Happy Eater to everyone in the mailing list ^____^

Shavonne Wijesinghe

----- Original Message -----
From: "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Thursday, April 05, 2007 12:05 PM
Subject: Re: [SQL] Insert only if not found

> am Thu, dem 05.04.2007, um 11:47:35 +0200 mailte Shavonne Marietta
> Wijesinghe folgendes:
>> I think i spoke to soon. It works. But if i change a letter from capital
>> to
>> simple it inserts my record 2 times. So i have 2 records for "Shavonne"
>> and
>> "shavonne". So i thought i would do the select in uppercase.
>>
>> INSERT INTO MOD48_02 (ID, TE_INDI, TE_COGNOME, TE_NOME, TE_SESSO,
>> TE_ATTNASC, TE_LUONASC, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA,
>> TE_RICHIESTA, USERNAME, DATE_INSERTED, TIME_INSERTED) SELECT
>> '127001200745114035', '', 'chan', 'micia', 'F', '', '', '01/05/2006', '',
>> '', '', '', 'demo', '05/04/2007', '11.40.35' WHERE NOT EXISTS (SELECT
>> upper(TE_COGNOME), upper(TE_NOME), upper(TE_SESSO), TE_DTNASC FROM
>> MOD48_02
>> WHERE TE_COGNOME='CHAN' AND TE_NOME='MICIA' AND TE_SESSO='F' AND
>> TE_DTNASC='01/05/2006');
>>
>>
>> but this doesn't work. I don't know why. If i try only the part
>
> You have an error ;-)
>
> Compare the strings in ther WHERE-clause. An example:
>
> test=# create table no_dupes (id int, name text);
> CREATE TABLE
> test=*# commit;
> COMMIT
> test=# insert into no_dupes select 1, 'Andreas' where not exists (select
> id, lower(name) from no_dupes where id=1 and
> lower(name)=lower('Andreas'));
> INSERT 0 1
> test=*# insert into no_dupes select 1, 'Andreas' where not exists (select
> id, lower(name) from no_dupes where id=1 and
> lower(name)=lower('Andreas'));
> INSERT 0 0
> test=*# select * from no_dupes ;
> id | name
> ----+---------
> 1 | Andreas
> (1 row)
>
> test=*# insert into no_dupes select 1, 'Andreas' where not exists (select
> id, name from no_dupes where id=1 and lower(name)=lower('Andreas'));
> INSERT 0 0
> test=*# select * from no_dupes ;
> id | name
> ----+---------
> 1 | Andreas
> (1 row)
>
> test=*#
>
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq