Thread: Table lookup in Postgresql

Hi All

I have a table that contains arrays of integers.  These integers
are code names for quantities. Each quantity (hence integer) has also
an associated index in a bigger array.

For example

Row in Table A
{5,6,7,8,9}

Table B contains the look up for the indices

Val   Idx
5      10
6       1
7       9
8       2
9       3

What I would like to do is to create a row in table C for all rows in table A that contains a vector where the indices corresponding to the elements in the A table are 1 and the remainder are 0 like

{ 0,1, 1,0,0,1,0,0,1,1}

where the maximum length of this vector is known

How can I best do it in Postgresql? Should I loop using PL/PgSQL?

Thanks
Mehmet


 

Mehmet Yunt wrote
> Hi All
>
> I have a table that contains arrays of integers.  These integers
> are code names for quantities. Each quantity (hence integer) has also
> an associated index in a bigger array.
>
> For example
>
> Row in Table A
> {5,6,7,8,9}
>
> Table B contains the look up for the indices
>
> Val   Idx
> 5      10
> 6       1
> 7       9
> 8       2
> 9       3
>
> What I would like to do is to create a row in table C for all rows in
> table
> A that contains a vector where the indices corresponding to the elements
> in
> the A table are 1 and the remainder are 0 like
>
> { 0,1, 1,0,0,1,0,0,1,1}
>
> where the maximum length of this vector is known
>
> How can I best do it in Postgresql? Should I loop using PL/PgSQL?
>
> Thanks
> Mehmet

You example result does not appear to match the true result given the input
data you have provided.  The true result should be {1,1,1,0,0,0,0,0,1,1}
assuming the maximum index is 10.

WITH tbl_a AS ( SELECT ARRAY[5,6,7,8,9]::integer[] AS actuals )   /* your
array input above */
   , tbl_b AS ( SELECT * FROM (VALUES(5,10),(6,1),(7,9),(8,2),(9,3)) t_b
(in_int, out_int) ) /* the mapping table above */
   , tbl_master AS (SELECT generate_series(1,10) AS out_int_master) /*
integer sequence to get known indexes */
   , tbl_combine_raw AS (

SELECT *
FROM (SELECT * FROM tbl_a CROSS JOIN tbl_b WHERE in_int = ANY(actuals))
actuals_lookup
RIGHT JOIN tbl_master ON (out_int_master = out_int)

) /* use " = ANY " to select rows from the mapping that exist in the input
array and also right-join so that every index from master is represented.
*/
SELECT ARRAY_AGG(CASE WHEN out_int IS NULL THEN 0 ELSE 1 END ORDER BY
out_int_master) AS mapped
FROM tbl_combine_raw
/* use a case statement to query whether each index is matched (1) or not
(0) and combined the results using array_agg with an order by clause to make
sure ordering is correct

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Table-lookup-in-Postgresql-tp5749327p5749345.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

In the example, I made an error in mapping the numbers to indices.  It should
be  {1,1,1,0,0,0,0,0,1,1}


Thank you David for the solution and pointing out the error.

Mehmet



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Table-lookup-in-Postgresql-tp5749327p5749455.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.