From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Insert only if not found
Date: Thu, 5 Apr 2007 12:05:55 +0200
Message-ID: <20070405100555.GG24814@a-kretschmer.de> (raw)
In-Reply-To: <008801c77767$7109c130$1102a8c0@dream>
References: <005b01c7775d$c4fa4b70$1102a8c0@dream>
<20070405085343.GF24814@a-kretschmer.de>
<007601c77764$2b2a0ec0$1102a8c0@dream>
<008801c77767$7109c130$1102a8c0@dream>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: Insert only if not found
In-Reply-To: <20070405100555.GG24814@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox