Re: [SQL] plpgsql functions and NULLs
My outer query to get the candidates has an outer join, that works just fine and I get the null OMCR_ID's. It's when I have to query the dimension table (no joins) to see if a row exists with a (sometimes) null OMCR_ID I'm forced to write 2 queries, when I think I should only have to write one. Thanks. -Don On Mon, 31 Jan 2005 14:25:03 -0600, Thomas F. O'Connell [EMAIL PROTECTED] wrote: This sounds like a perfect candidate for a LEFT OUTER JOIN. See: http://www.postgresql.org/docs/7.4/static/queries-table- expressions.html#QUERIES-FROM Yours would looks something like: SELECT * FROM ... LEFT JOIN candidate AS c ON omcr_id = c.omcr_id AND ... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 30, 2005, at 1:41 PM, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; IF FOUND ... Is there a way to do the lookup in one statement?? This could get ugly quick. I'm using v7.4. Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] plpgsql functions and NULLs
As far as I know, you didn't post your actual table definitions (or full queries) earlier, so I'm not exactly sure what you mean. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 31, 2005, at 3:06 PM, Don Drake wrote: My outer query to get the candidates has an outer join, that works just fine and I get the null OMCR_ID's. It's when I have to query the dimension table (no joins) to see if a row exists with a (sometimes) null OMCR_ID I'm forced to write 2 queries, when I think I should only have to write one. Thanks. -Don ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] plpgsql functions and NULLs
On Sun, 30 Jan 2005, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] plpgsql functions and NULLs
You learn something new everyday. I've never seen that syntax before, and it works like a charm!! Thanks a ton. -Don On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo [EMAIL PROTECTED] wrote: On Sun, 30 Jan 2005, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 3: 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
Re: [SQL] plpgsql functions and NULLs
On Mon, 31 Jan 2005, Don Drake wrote: You learn something new everyday. I've never seen that syntax before, and it works like a charm!! Actually, now that I think about it, I wonder if that's a good thing to use because I don't think that'll use indexes to do the search. You may want to do some testing to see how it runs for you. On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo [EMAIL PROTECTED] wrote: On Sun, 30 Jan 2005, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql functions and NULLs
Don Drake [EMAIL PROTECTED] writes: On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo [EMAIL PROTECTED] wrote: Actually, now that I think about it, I wonder if that's a good thing to use because I don't think that'll use indexes to do the search. You may want to do some testing to see how it runs for you. I'm constraining on other columns as well and it's still picking up the index. Stephan is right that an IS DISTINCT FROM construct is not considered indexable. So it's only your other constraints that are being used with the index. You need to think about whether the other constraints are selective enough to yield adequate performance. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] plpgsql functions and NULLs
OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; IF FOUND ... Is there a way to do the lookup in one statement?? This could get ugly quick. I'm using v7.4. Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
