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