Thread: Simple SQL question
Hello, everybody.
I've a simple question about SQL usage but i don't know even i can
formulate this question. Well, i will try : I've a teacher_course table with columns cd_course, cd_teacher =>
teacher_course(cd_teacher,cd_course) and i've a set of pairs that
contains the values for these columns, like {(1,2),(23,11),(563,341),...}. Is there a way to build a query to select
rowsthat matchs these
pairs ? Like a "in" extension (or something else) :
select * from teacher_course where (cd_course,cd_teacher) in
((1,2),(23,11),(563,341))
?
I would appreciate any help. Thanks in advance.
Thank you, Jeremy.
I've built a function that returns a string from (cd_teacher, cd_course)
and i've create a functional index over this function, like :
create index teacher_course_idx on teacher_course
(build_unique_2p(cd_teacher,cd_course));
select * from teacher_course where build_unique_2p(cd_teacher,cd_course)
in ('1:2','23:11','563','341');
Is it possible to use "array cast" over cd_teacher and cd_course (just
an idea, i don't known the sintaxe), like:
select * from teachar_course where cast((cd_teacher,cd_course) as array)
in ('{1,2}','{23,11}','{563,341}');
?
I'm using postgreSQL 7.34
Thanks !!!
Jeremy Semeiks wrote:
>You could use a subselect of unions:
>
>select * from teacher_course where (cd_course, cd_teacher) in
>(select 1, 2 union select 23, 11 union select 563, 341)
>
>Maybe there's a more concise way, though.
>
>- Jeremy
>
>
>
Just a note based on my experience, if you are going to to use IN processing
then there is a good chance that the index isn't going to be used. In some
recent tests I did, the index stopped being used after I put 3 or more items
in the IN list. You should build some representatve examples of the select
and test them using "ANALYSE SELECT ..." .I'm using 7.4.1.
The result from the select is the same, even if you don't make an index on
the function result.It's just a performance consideration. If the index
isn't used, then you don't need to create it.
regards
Iain
----- Original Message -----
From: "Daniel Henrique Alves Lima" <email_daniel_h@yahoo.com.br>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 09, 2004 1:21 PM
Subject: Re: [SQL] Simple SQL question
> Thank you, Jeremy.
>
> I've built a function that returns a string from (cd_teacher, cd_course)
> and i've create a functional index over this function, like :
>
> create index teacher_course_idx on teacher_course
> (build_unique_2p(cd_teacher,cd_course));
> select * from teacher_course where build_unique_2p(cd_teacher,cd_course)
> in ('1:2','23:11','563','341');
>
> Is it possible to use "array cast" over cd_teacher and cd_course (just
> an idea, i don't known the sintaxe), like:
>
> select * from teachar_course where cast((cd_teacher,cd_course) as array)
> in ('{1,2}','{23,11}','{563,341}');
>
> ?
>
> I'm using postgreSQL 7.34
>
> Thanks !!!
>
> Jeremy Semeiks wrote:
>
> >You could use a subselect of unions:
> >
> >select * from teacher_course where (cd_course, cd_teacher) in
> >(select 1, 2 union select 23, 11 union select 563, 341)
> >
> >Maybe there's a more concise way, though.
> >
> >- Jeremy
> >
> >
> >
>
>
>
> ---------------------------(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
Ok, Iain. Thanks for the tip ! Iain wrote: >Just a note based on my experience, if you are going to to use IN processing >then there is a good chance that the index isn't going to be used. In some >recent tests I did, the index stopped being used after I put 3 or more items >in the IN list. You should build some representatve examples of the select >and test them using "ANALYSE SELECT ..." .I'm using 7.4.1. > >The result from the select is the same, even if you don't make an index on >the function result.It's just a performance consideration. If the index >isn't used, then you don't need to create it. > >regards >Iain >----- Original Message ----- >From: "Daniel Henrique Alves Lima" <email_daniel_h@yahoo.com.br> >To: <pgsql-sql@postgresql.org> >Sent: Tuesday, March 09, 2004 1:21 PM >Subject: Re: [SQL] Simple SQL question > > > >
Sorry, did I write "ANALYSE SELECT .... "?
It's supposed to be "EXPLAIN [ANALYSE] [VERBOSE] SELECT ..."
----- Original Message -----
From: "Daniel Henrique Alves Lima" <email_daniel_h@yahoo.com.br>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 09, 2004 1:21 PM
Subject: Re: [SQL] Simple SQL question
> Thank you, Jeremy.
>
> I've built a function that returns a string from (cd_teacher, cd_course)
> and i've create a functional index over this function, like :
>
> create index teacher_course_idx on teacher_course
> (build_unique_2p(cd_teacher,cd_course));
> select * from teacher_course where build_unique_2p(cd_teacher,cd_course)
> in ('1:2','23:11','563','341');
>
> Is it possible to use "array cast" over cd_teacher and cd_course (just
> an idea, i don't known the sintaxe), like:
>
> select * from teachar_course where cast((cd_teacher,cd_course) as array)
> in ('{1,2}','{23,11}','{563,341}');
>
> ?
>
> I'm using postgreSQL 7.34
>
> Thanks !!!
>
> Jeremy Semeiks wrote:
>
> >You could use a subselect of unions:
> >
> >select * from teacher_course where (cd_course, cd_teacher) in
> >(select 1, 2 union select 23, 11 union select 563, 341)
> >
> >Maybe there's a more concise way, though.
> >
> >- Jeremy
> >
> >
> >
>
>
>
> ---------------------------(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