Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6FC5C9FBA9F for ; Thu, 5 Apr 2007 07:06:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 83679-01 for ; Thu, 5 Apr 2007 07:05:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.187]) by postgresql.org (Postfix) with ESMTP id A7DB39FBA64 for ; Thu, 5 Apr 2007 07:06:02 -0300 (ADT) Received: from [194.25.154.138] (helo=webserv.wug.de) by mrelayeu.kundenserver.de (node=mrelayeu6) with ESMTP (Nemesis), id 0ML29c-1HZOqq2OV8-0003R3; Thu, 05 Apr 2007 12:05:57 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1HZOqp-0000Cl-00 for pgsql-sql@postgresql.org; Thu, 05 Apr 2007 12:05:55 +0200 Date: Thu, 5 Apr 2007 12:05:55 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Insert only if not found Message-ID: <20070405100555.GG24814@a-kretschmer.de> References: <005b01c7775d$c4fa4b70$1102a8c0@dream> <20070405085343.GF24814@a-kretschmer.de> <007601c77764$2b2a0ec0$1102a8c0@dream> <008801c77767$7109c130$1102a8c0@dream> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <008801c77767$7109c130$1102a8c0@dream> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX191xM2i+Sh0AreOmL9y/A5pyCihrbaiL6OhMCU o07DqgJKFQ5UJIDry6pDsXq8NlLKmmFvqRmG5ZBDEn1BS81Y9Q A/cTmzjjemA3AFTTsX2nUHxsEvgzF/A X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/72 X-Sequence-Number: 28072 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