From shavonne.marietta@studioform.it Thu Apr 5 08:38:10 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id EAE8C9FBD40 for ; Thu, 5 Apr 2007 05:38:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 49872-01-5 for ; Thu, 5 Apr 2007 05:37:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtpout27.attiva.biz (smtpout27.attiva.biz [85.37.16.28]) by postgresql.org (Postfix) with ESMTP id AA7A79FBD16 for ; Thu, 5 Apr 2007 05:37:57 -0300 (ADT) Received: from fbcmfe02b11.fbc.local ([192.168.30.92]) by smtpout27.attiva.biz with Microsoft SMTPSVC(6.0.3790.1830); Thu, 5 Apr 2007 10:37:55 +0200 Received: from dream ([88.34.161.130]) by fbcmfe02b11.fbc.local with Microsoft SMTPSVC(6.0.3790.1830); Thu, 5 Apr 2007 10:37:54 +0200 Message-ID: <005b01c7775d$c4fa4b70$1102a8c0@dream> Reply-To: "Shavonne Marietta Wijesinghe" From: "Shavonne Marietta Wijesinghe" To: Subject: Insert only if not found Date: Thu, 5 Apr 2007 10:38:21 +0200 Organization: Studio FORM MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0058_01C7776E.882FA440" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-OriginalArrivalTime: 05 Apr 2007 08:37:54.0839 (UTC) FILETIME=[B490A270:01C7775D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.342 tagged_above=0 required=5 tests=AWL=-0.156, BAYES_50=0.001, HTML_40_50=0.496, HTML_MESSAGE=0.001, UPPERCASE_25_50=0 X-Spam-Level: X-Archive-Number: 200704/68 X-Sequence-Number: 28068 This is a multi-part message in MIME format. ------=_NextPart_000_0058_01C7776E.882FA440 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone.. Here is the result of my Insert. and also the insert works ok. 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) VALUES = ('127001200745101033', 'england', 'bloom', 'orlando', 'M', 'ww', = 'florida', '05/06/1984', 'america', 'UK', 'xx', 'no', 'demo', = '05/04/2007', '10.10.33'); But before i insert this record i want to check if the it hasn't been = inserted before. I want to use the following fields as the riefferent. = TE_COGNOME, TE_NOME, TE_SESSO, TE_DTNASC And if there is no record to match those 4 fields i want to insert it = else just a message saying that the record has been inserted b4. any idea to start with? Shavonne Wijesinghe ------=_NextPart_000_0058_01C7776E.882FA440 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello everyone..
 
Here is the result of my Insert. and = also the=20 insert works ok.
 
INSERT INTO MOD48_02 (ID, = TE_INDI,=20 TE_COGNOME, TE_NOME, TE_SESSO, TE_ATTNASC, TE_LUONASC, TE_DTNASC, = TE_PROVSTATO,=20 TE_PROV, TE_PATERNITA, TE_RICHIESTA, USERNAME, DATE_INSERTED, = TIME_INSERTED)=20 VALUES ('127001200745101033', 'england', 'bloom', 'orlando', 'M', 'ww',=20 'florida', '05/06/1984', 'america', 'UK', 'xx', 'no', 'demo', = '05/04/2007',=20 '10.10.33');
But before i insert this record i want = to check if=20 the it hasn't been inserted before. I want to use the following fields = as the=20 riefferent. TE_COGNOME, TE_NOME, TE_SESSO, TE_DTNASC
 
And if there is no record to match = those 4 fields i=20 want to insert it else just a message saying that the record has been = inserted=20 b4.
 
any idea to start with?
 
Shavonne = Wijesinghe
------=_NextPart_000_0058_01C7776E.882FA440-- From andreas.kretschmer@schollglas.com Thu Apr 5 08:53:57 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D2CC69FBCAC for ; Thu, 5 Apr 2007 05:53:57 -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 51198-05 for ; Thu, 5 Apr 2007 05:53:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.183]) by postgresql.org (Postfix) with ESMTP id 5E2939FB9BD for ; Thu, 5 Apr 2007 05:53:51 -0300 (ADT) Received: from [194.25.154.138] (helo=webserv.wug.de) by mrelayeu.kundenserver.de (node=mrelayeu0) with ESMTP (Nemesis), id 0MKwh2-1HZNiy2mTX-0003y8; Thu, 05 Apr 2007 10:53:45 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1HZNix-00088h-00 for pgsql-sql@postgresql.org; Thu, 05 Apr 2007 10:53:43 +0200 Date: Thu, 5 Apr 2007 10:53:43 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Insert only if not found Message-ID: <20070405085343.GF24814@a-kretschmer.de> References: <005b01c7775d$c4fa4b70$1102a8c0@dream> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <005b01c7775d$c4fa4b70$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: V01U2FsdGVkX19l4njQqYZ5nzYMpUtZ547ltCu6CVnaAAaL7GM aJh49g/4LcfgCrPfsc3qRuJU4v4X7+SwXU+anipbEO3tkIgL9o AbSM9Iy1AUK/xi4egWp1xGl837k5Ggq X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/69 X-Sequence-Number: 28069 am Thu, dem 05.04.2007, um 10:38:21 +0200 mailte Shavonne Marietta Wijesinghe folgendes: > Hello everyone.. > > Here is the result of my Insert. and also the insert works ok. > > 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) VALUES ('127001200745101033', > 'england', 'bloom', 'orlando', 'M', 'ww', 'florida', '05/06/1984', 'america', > 'UK', 'xx', 'no', 'demo', '05/04/2007', '10.10.33'); > But before i insert this record i want to check if the it hasn't been inserted > before. I want to use the following fields as the riefferent. TE_COGNOME, > TE_NOME, TE_SESSO, TE_DTNASC > > And if there is no record to match those 4 fields i want to insert it else just > a message saying that the record has been inserted b4. > > any idea to start with? Yes, read this: http://ads.wars-nicht.de/blog/archives/11-Avoid-Unique-Key-violation.html I think, you can change this for your request. (but without the message, if you really need this message, you should use a function and RAISE a NOTICE as the message) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net From shavonne.marietta@studioform.it Thu Apr 5 09:24:06 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 86CAA9FBC89 for ; Thu, 5 Apr 2007 06:24:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 77279-05 for ; Thu, 5 Apr 2007 06:23:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtpout13.attiva.biz (smtpout13.attiva.biz [85.37.16.15]) by postgresql.org (Postfix) with ESMTP id 424079FBC50 for ; Thu, 5 Apr 2007 06:24:01 -0300 (ADT) Received: from FBCMFE02B08.fbc.local ([192.168.30.96]) by smtpout13.attiva.biz with Microsoft SMTPSVC(6.0.3790.1830); Thu, 5 Apr 2007 11:23:52 +0200 Received: from dream ([88.34.161.130]) by FBCMFE02B08.fbc.local with Microsoft SMTPSVC(6.0.3790.1830); Thu, 5 Apr 2007 11:23:43 +0200 Message-ID: <007601c77764$2b2a0ec0$1102a8c0@dream> Reply-To: "Shavonne Marietta Wijesinghe" From: "Shavonne Marietta Wijesinghe" To: References: <005b01c7775d$c4fa4b70$1102a8c0@dream> <20070405085343.GF24814@a-kretschmer.de> Subject: Re: Insert only if not found Date: Thu, 5 Apr 2007 11:24:07 +0200 Organization: Studio FORM MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-OriginalArrivalTime: 05 Apr 2007 09:23:43.0216 (UTC) FILETIME=[1AB9AF00:01C77764] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/70 X-Sequence-Number: 28070 wow thanks. i managed to paste it to fit my code.. yeahhhh ^____^ Shavonne Wijesinghe www.studioform.it ----- Original Message ----- From: "A. Kretschmer" To: Sent: Thursday, April 05, 2007 10:53 AM Subject: Re: [SQL] Insert only if not found > am Thu, dem 05.04.2007, um 10:38:21 +0200 mailte Shavonne Marietta > Wijesinghe folgendes: >> Hello everyone.. >> >> Here is the result of my Insert. and also the insert works ok. >> >> 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) VALUES ('127001200745101033', >> 'england', 'bloom', 'orlando', 'M', 'ww', 'florida', '05/06/1984', >> 'america', >> 'UK', 'xx', 'no', 'demo', '05/04/2007', '10.10.33'); >> But before i insert this record i want to check if the it hasn't been >> inserted >> before. I want to use the following fields as the riefferent. TE_COGNOME, >> TE_NOME, TE_SESSO, TE_DTNASC >> >> And if there is no record to match those 4 fields i want to insert it >> else just >> a message saying that the record has been inserted b4. >> >> any idea to start with? > > Yes, read this: > http://ads.wars-nicht.de/blog/archives/11-Avoid-Unique-Key-violation.html > > > I think, you can change this for your request. > (but without the message, if you really need this message, you should use > a function and RAISE a NOTICE as the message) > > > 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 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly From shavonne.marietta@studioform.it Thu Apr 5 09:47:14 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id DDA379FB459 for ; Thu, 5 Apr 2007 06:47:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 74620-07 for ; Thu, 5 Apr 2007 06:47:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtpout26.attiva.biz (smtpout26.attiva.biz [85.37.16.27]) by postgresql.org (Postfix) with ESMTP id 01BCF9FA13B for ; Thu, 5 Apr 2007 06:47:10 -0300 (ADT) Received: from FBCMFE02B07.fbc.local ([192.168.30.95]) by smtpout26.attiva.biz with Microsoft SMTPSVC(6.0.3790.1830); Thu, 5 Apr 2007 11:47:09 +0200 Received: from dream ([88.34.161.130]) by FBCMFE02B07.fbc.local with Microsoft SMTPSVC(6.0.3790.1830); Thu, 5 Apr 2007 11:47:08 +0200 Message-ID: <008801c77767$7109c130$1102a8c0@dream> Reply-To: "Shavonne Marietta Wijesinghe" From: "Shavonne Marietta Wijesinghe" To: References: <005b01c7775d$c4fa4b70$1102a8c0@dream> <20070405085343.GF24814@a-kretschmer.de> <007601c77764$2b2a0ec0$1102a8c0@dream> Subject: Re: Insert only if not found Date: Thu, 5 Apr 2007 11:47:35 +0200 Organization: Studio FORM MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-OriginalArrivalTime: 05 Apr 2007 09:47:08.0940 (UTC) FILETIME=[609A24C0:01C77767] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/71 X-Sequence-Number: 28071 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 SELECT upper(TE_COGNOME), upper(TE_NOME), upper(TE_SESSO), TE_DTNASC FROM MOD48_02 it returns me the fields in UPPERCASE. It's really strange why it doens't work all together. Anyone? ----- Original Message ----- From: "Shavonne Marietta Wijesinghe" To: Sent: Thursday, April 05, 2007 11:24 AM Subject: Re: [SQL] Insert only if not found > wow thanks. i managed to paste it to fit my code.. > yeahhhh ^____^ > > Shavonne Wijesinghe > www.studioform.it > > > ----- Original Message ----- > From: "A. Kretschmer" > To: > Sent: Thursday, April 05, 2007 10:53 AM > Subject: Re: [SQL] Insert only if not found > > >> am Thu, dem 05.04.2007, um 10:38:21 +0200 mailte Shavonne Marietta >> Wijesinghe folgendes: >>> Hello everyone.. >>> >>> Here is the result of my Insert. and also the insert works ok. >>> >>> 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) VALUES ('127001200745101033', >>> 'england', 'bloom', 'orlando', 'M', 'ww', 'florida', '05/06/1984', >>> 'america', >>> 'UK', 'xx', 'no', 'demo', '05/04/2007', '10.10.33'); >>> But before i insert this record i want to check if the it hasn't been >>> inserted >>> before. I want to use the following fields as the riefferent. >>> TE_COGNOME, >>> TE_NOME, TE_SESSO, TE_DTNASC >>> >>> And if there is no record to match those 4 fields i want to insert it >>> else just >>> a message saying that the record has been inserted b4. >>> >>> any idea to start with? >> >> Yes, read this: >> http://ads.wars-nicht.de/blog/archives/11-Avoid-Unique-Key-violation.html >> >> >> I think, you can change this for your request. >> (but without the message, if you really need this message, you should use >> a function and RAISE a NOTICE as the message) >> >> >> 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 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate From andreas.kretschmer@schollglas.com Thu Apr 5 10:06:08 2007 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 From roger77_lb@yahoo.com Thu Apr 5 22:47:18 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7C2CB9FBCB7 for ; Thu, 5 Apr 2007 19:47:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 01335-05 for ; Thu, 5 Apr 2007 19:47:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web90609.mail.mud.yahoo.com (web90609.mail.mud.yahoo.com [216.252.100.192]) by postgresql.org (Postfix) with SMTP id 1DDC59FBB91 for ; Thu, 5 Apr 2007 19:47:11 -0300 (ADT) Received: (qmail 47983 invoked by uid 60001); 5 Apr 2007 22:47:09 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=Pg1A/WWl71dd7+fOmoQ4+FjZmBTgijeVOXNm+RRNPm7pgs8XRLIPYg8spUBFLtd2k8FGoXqTD0T2KOo/ln/yn18FemWub0Wz4yy5BwDj0lwBL2u68AO4T7to3QrmTG+zkCaenCf3cjRIokb5bR0JIPsFxMQit1ZJRyDQT/8xiPE=; X-YMail-OSG: dCmLV9IVM1l1dCkl8nywwYJeJOzHIXcYcNa9BkHwpTn57c.68m71Rsd0V8MYiP6a_6a3sXVmpLA9QouPFfIzkhaqyHpihz68yKxg0WLiF2uKcK_jaiH1KK34juDUtQ-- Received: from [88.85.65.133] by web90609.mail.mud.yahoo.com via HTTP; Thu, 05 Apr 2007 15:47:09 PDT Date: Thu, 5 Apr 2007 15:47:09 -0700 (PDT) From: Roger Tannous Subject: Re: Insert only if not found To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <817519.46561.qm@web90609.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.756 tagged_above=0 required=5 tests=BAYES_00=-2.599, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447 X-Spam-Level: X-Archive-Number: 200704/83 X-Sequence-Number: 28083 It's also desirable that you use TRIM along with UPPER or LOWER. Roger. ____________________________________________________________________________________ Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html From shavonne.marietta@studioform.it Fri Apr 6 07:09:42 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 382889FB62B for ; Fri, 6 Apr 2007 04:09:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81785-02-2 for ; Fri, 6 Apr 2007 04:09:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtpout24.attiva.biz (smtpout24.attiva.biz [85.37.16.25]) by postgresql.org (Postfix) with ESMTP id AA30E9FBDD3 for ; Fri, 6 Apr 2007 04:09:36 -0300 (ADT) Received: from fbcmfe02b10.fbc.local ([192.168.30.91]) by smtpout24.attiva.biz with Microsoft SMTPSVC(6.0.3790.1830); Fri, 6 Apr 2007 09:09:34 +0200 Received: from dream ([88.34.161.130]) by fbcmfe02b10.fbc.local with Microsoft SMTPSVC(6.0.3790.1830); Fri, 6 Apr 2007 09:09:34 +0200 Message-ID: <001201c7781a$987bee10$1102a8c0@dream> Reply-To: "Shavonne Marietta Wijesinghe" From: "Shavonne Marietta Wijesinghe" To: References: <005b01c7775d$c4fa4b70$1102a8c0@dream> <20070405085343.GF24814@a-kretschmer.de> <007601c77764$2b2a0ec0$1102a8c0@dream> <008801c77767$7109c130$1102a8c0@dream> <20070405100555.GG24814@a-kretschmer.de> Subject: Re: Insert only if not found Date: Fri, 6 Apr 2007 09:10:00 +0200 Organization: Studio FORM MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-OriginalArrivalTime: 06 Apr 2007 07:09:34.0172 (UTC) FILETIME=[8788C9C0:01C7781A] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/84 X-Sequence-Number: 28084 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" To: 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