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" <[EMAIL PROTECTED]>
To: <pgsql-sql@postgresql.org>
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" <[EMAIL PROTECTED]>
To: <pgsql-sql@postgresql.org>
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 [EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
