Thread: Insert only if not found

Insert only if not found

From

"Shavonne Marietta Wijesinghe"

Date:

05 April 2007, 08:38:12

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

Re: Insert only if not found

From

"A. Kretschmer"

Date:

05 April 2007, 08:53:59

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

Re: Insert only if not found

From

"Shavonne Marietta Wijesinghe"

Date:

05 April 2007, 09:24:08

wow thanks. i managed to paste it to fit my code..
yeahhhh ^____^

Shavonne Wijesinghe
www.studioform.it


----- Original Message ----- 
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
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 majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: Insert only if not found

From

"Shavonne Marietta Wijesinghe"

Date:

05 April 2007, 09:47:15

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" <shavonne.marietta@studioform.it>
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" <andreas.kretschmer@schollglas.com>
> 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 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

Re: Insert only if not found

From

"A. Kretschmer"

Date:

05 April 2007, 10:06:11

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

Re: Insert only if not found

From

Roger Tannous

Date:

05 April 2007, 22:47:19

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

Re: Insert only if not found

From

"Shavonne Marietta Wijesinghe"

Date:

06 April 2007, 07:09:44

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" <andreas.kretschmer@schollglas.com>
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