pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin on a like
3+ messages / 2 participants
[nested] [flat]
* join on a like
@ 2006-05-01 10:14 Sim Zacks <sim@compulab.co.il>
0 siblings, 1 reply; 3+ messages in thread
From: Sim Zacks @ 2006-05-01 10:14 UTC (permalink / raw)
To: pgsql-sql
A friend suggested that I use the Like predicate as part of my join
condition and I was wondering if that was wise.
For example, I have a column that describes the level of an event.
There is A,B,C and then they can have children, such as AA,AB,BA,BB and
they can all have children as well.
So if I wanted to see all the children with their parents at every level
it would be:
select a.* from tbl1 a join tbl1 b on a.level like b.level || '%'
That would give me on one side A and on the other side A,AA,AB,AAAB,...
as well as AA and on the other side all of its children.
I'm just nervous about using a Like in a join.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: join on a like
@ 2006-05-01 11:45 mlartz@gmail.com <mlartz@gmail.com>
parent: Sim Zacks <sim@compulab.co.il>
0 siblings, 1 reply; 3+ messages in thread
From: mlartz@gmail.com @ 2006-05-01 11:45 UTC (permalink / raw)
To: pgsql-sql
If you use a btree index on the column, you should be able to use it in
the like:
http://www.postgresql.org/docs/8.1/interactive/indexes-types.html.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: join on a like
@ 2006-05-02 08:41 Sim Zacks <sim@compulab.co.il>
parent: mlartz@gmail.com <mlartz@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Sim Zacks @ 2006-05-02 08:41 UTC (permalink / raw)
To: pgsql-sql
I guess the real question is:
what is more efficient - a join on a LIKE or a PLPGSQL function that
has 2 loops, 1 for each bottom level child and one that takes the
substring of that child one character at a time to get each parent?
It sounds like you are saying that the join will actaully use the index
and therefore it is optimal, not just possible.
mlartz@gmail.com wrote:
> If you use a btree index on the column, you should be able to use it in
> the like:
> http://www.postgresql.org/docs/8.1/interactive/indexes-types.html.
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-02 08:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-01 10:14 join on a like Sim Zacks <sim@compulab.co.il>
2006-05-01 11:45 ` mlartz@gmail.com <mlartz@gmail.com>
2006-05-02 08:41 ` Sim Zacks <sim@compulab.co.il>
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