Thread: 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
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
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 <tfo@sitening.com> 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
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
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)
Dear Friends how i can increse the number of commands in an transaction block i use postgres 7.4.5 on linux Regards Luiz ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone.bigpanda.com> To: "Don Drake" <dondrake@gmail.com> Cc: <pgsql-sql@postgresql.org> Sent: Monday, January 31, 2005 7:31 PM Subject: 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 > > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.8.2 - Release Date: 28/1/2005 > >
On Mon, Jan 31, 2005 at 07:54:45PM -0200, Luiz Rafael Culik Guimaraes wrote: > > how i can increse the number of commands in an transaction block What do you mean? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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 <sszabo@megazone.bigpanda.com> 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
Hi Michael Fuhr >> how i can increse the number of commands in an transaction block > > What do you mean? What problem are you trying to solve? i´m trying to solve the follow message current transaction is aborted, queries ignored until end of transaction block some one tell me this is defined inside postgres sources i recive this message when i execute an certain number of queries inside an begin/commit block Regards Luiz
On Mon, 2005-01-31 at 16:29, Luiz Rafael Culik Guimaraes wrote: > Hi Michael Fuhr > >> how i can increse the number of commands in an transaction block > > > > What do you mean? What problem are you trying to solve? > > i´m trying to solve the follow message > current transaction is aborted, queries ignored until end of transaction > block > > some one tell me this is defined inside postgres sources > i recive this message when i execute an certain number of queries inside an > begin/commit block This is normal postgresql behaviour, and can't really be changed. However, with the advent of savepoints in 8.0, it is now possible to detect such errors and roll back so you can then continue. Without using savepoints with rollback to the given save point, however, there's not much way to get around it. It sounds to me like you're getting an error somewhere in your scripts you need to look into.
On Mon, Jan 31, 2005 at 08:29:42PM -0200, Luiz Rafael Culik Guimaraes wrote: > > i´m trying to solve the follow message > current transaction is aborted, queries ignored until end of transaction > block A previous command in the transaction has failed; no more commands will be executed until you issue a ROLLBACK (or a COMMIT, but the transaction will be rolled back due to the error). If you're doing error checking on every command then you should be able to discover which command failed. PostgreSQL 8.0 has savepoints so you can roll back part of a transaction and continue after an error, but that might not be what you need. > some one tell me this is defined inside postgres sources > i recive this message when i execute an certain number of queries inside an > begin/commit block Transactions can have 2^32 - 1 (4294967295) commands, so I'd be surprised if you were hitting that limit. If you were, you should see the following error: cannot have more than 2^32-1 commands in a transaction -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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 > <sszabo@megazone.bigpanda.com> 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)
I'm constraining on other columns as well and it's still picking up the index. Thanks again. -Don On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > 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 > > <sszabo@megazone.bigpanda.com> 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
Don Drake <dondrake@gmail.com> writes:
> On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo
> <sszabo@megazone.bigpanda.com> 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