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" <[EMAIL PROTECTED]>
To: <pgsql-sql@postgresql.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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
