Re: [SQL] Insert only if not found
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" <[EMAIL PROTECTED]>
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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Update Field with function/data from other tables?
Hi all, I want to update the values of a column with the result of a function that requires information from another table. Specifically it needs a minium and maximum value from another table. How do I perform the update query? Below is my attempt but it doesn't work. The "bhlineid" is my primary key and foreign key in the 2 tables UPDATE geo_obs.bhline SET bhline_geom = CalcBH3dLineMC (minmax.bhlineid, minmax.srid_original, minmax.from_m, minmax.to_m, 'geo_obs.bhline_surv', 'bhlineid', 'dist_m', 'azim', 'v_inclin', 'bhline_surv_geom', 5.0) FROM (SELECT i.bhlineid AS bhlineid, min(i.from_m) AS from_m, max(i.to_m) AS to_m, max(c.srid_original) AS srid_original FROM geo_obs.bhline_int AS i, geo_obs.bhline AS c WHERE c.bhlineid=i.bhlineid GROUP BY i.bhlineid) AS minmax WHERE geo_obs.bhline.bhlineid = minmax.bhlineid ; Thanks for any help. Phillip Allen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] dropping a schema and cross-schema dependencies
I would like to be able to drop a schema with all of its objects, but don't want to accidentally drop objects that are in other schemas. If there are such objects I'd prefer the drop to fail. If I use DROP SCHEMA FOO CASCADE I run the risk of dropping objects from other schemas that depend on objects in FOO (e.g. a table that uses a domain/user defined datatype from FOO). I don't particularly like such cross-schema dependencies but they are easy to create acidentally and I don't want to blow out a big table. Any thoughts on this? "Talk to the people who wrote the SQL standard" is one response, of course, but what do you all think is a reasonable behavior here? Anyone else see this as an issue? Does this warrant an PG extension to SQL? How do other DBMSs do it? As an immediate solution can anyone share a comprehensive query to INFORMATION_SCHEMA/PG_CATALOG that can be used to check for any cross-schema dependencies and halt before issuing a DROP with a CASCADE? TIA, George ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] dropping a schema and cross-schema dependencies
"George Pavlov" <[EMAIL PROTECTED]> writes: > As an immediate solution can anyone share a comprehensive query to > INFORMATION_SCHEMA/PG_CATALOG that can be used to check for any > cross-schema dependencies and halt before issuing a DROP with a CASCADE? Issue the DROP without CASCADE and read the error message. regards, tom lane ---(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
Re: [SQL] dropping a schema and cross-schema dependencies
> > As an immediate solution can anyone share a comprehensive query to > > INFORMATION_SCHEMA/PG_CATALOG that can be used to check for any > > cross-schema dependencies and halt before issuing a DROP > with a CASCADE? > > Issue the DROP without CASCADE and read the error message. Well, to be precise I should read the notices preceding the error message (the error message is the same whether there are within- or cross-schema dependencies). But, yes, that's what I have been doing, I was hoping for something a little more graceful. Ideally I want a DROP SCHEMA in a script to succeed without bothering me if there are no cross-schema dependencies and only to fail if there are cross-schema dependencies. So I was hoping I could check ahead of time and in a definitive way that there are no cross schema dependencies and then issue a DROP CASCADE. What you are suggesting is to issue a DROP without CASCADE and grep through the error message before issuing a second DROP CASCADE, but what am I grepping for? What are all the possible notices that pertain to cross-schema dependencies? I want to make sure I am not missing something that I have not seen before. So, for example we might have a notice like this: NOTICE: table test.employee column first_name depends on type name_ud So, if the word "table" is followed by a word that contains a period I have a cross-schema dependency. This could work. I just want to be comprehensive. George ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
