pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql functions and NULLs
9+ messages / 4 participants
[nested] [flat]
* plpgsql functions and NULLs
@ 2005-01-30 19:41 Don Drake <dondrake@gmail.com>
2005-01-31 20:25 ` Re: plpgsql functions and NULLs Thomas F.O'Connell <tfo@sitening.com>
2005-01-31 21:31 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 9+ messages in thread
From: Don Drake @ 2005-01-30 19:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
@ 2005-01-31 20:25 ` Thomas F.O'Connell <tfo@sitening.com>
2005-01-31 21:06 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Thomas F.O'Connell @ 2005-01-31 20:25 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-01-31 20:25 ` Re: plpgsql functions and NULLs Thomas F.O'Connell <tfo@sitening.com>
@ 2005-01-31 21:06 ` Don Drake <dondrake@gmail.com>
2005-01-31 21:14 ` Re: plpgsql functions and NULLs Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 9+ messages in thread
From: Don Drake @ 2005-01-31 21:06 UTC (permalink / raw)
To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-01-31 20:25 ` Re: plpgsql functions and NULLs Thomas F.O'Connell <tfo@sitening.com>
2005-01-31 21:06 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
@ 2005-01-31 21:14 ` Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 9+ messages in thread
From: Thomas F.O'Connell @ 2005-01-31 21:14 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
@ 2005-01-31 21:31 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-01-31 22:27 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2005-01-31 21:31 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-01-31 21:31 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2005-01-31 22:27 ` Don Drake <dondrake@gmail.com>
2005-02-01 00:32 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 9+ messages in thread
From: Don Drake @ 2005-01-31 22:27 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-01-31 21:31 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-01-31 22:27 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
@ 2005-02-01 00:32 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-02-01 02:12 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2005-02-01 00:32 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-01-31 21:31 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-01-31 22:27 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-02-01 00:32 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2005-02-01 02:12 ` Don Drake <dondrake@gmail.com>
2005-02-01 03:37 ` Re: plpgsql functions and NULLs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Don Drake @ 2005-02-01 02:12 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql functions and NULLs
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-01-31 21:31 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-01-31 22:27 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-02-01 00:32 ` Re: plpgsql functions and NULLs Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-02-01 02:12 ` Re: plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
@ 2005-02-01 03:37 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2005-02-01 03:37 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-02-01 03:37 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-30 19:41 plpgsql functions and NULLs Don Drake <dondrake@gmail.com>
2005-01-31 20:25 ` Thomas F.O'Connell <tfo@sitening.com>
2005-01-31 21:06 ` Don Drake <dondrake@gmail.com>
2005-01-31 21:14 ` Thomas F.O'Connell <tfo@sitening.com>
2005-01-31 21:31 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-01-31 22:27 ` Don Drake <dondrake@gmail.com>
2005-02-01 00:32 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-02-01 02:12 ` Don Drake <dondrake@gmail.com>
2005-02-01 03:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox