Converting id's in int[] elements in another table Postgres

Question

I have a table that contains an array of ints (we'll call it lineTable). That array of int's represents an array of unique id's in another table (pointTable). The point table in turn contains 3 columns (id, x, y).

I want to be able to convert each one of those ints in the lineTable.points to an array of of array of ints.

lineTable              pointTable
| id | points |        | id | x | y |
| int| int[]  |        | int|int|int|

lineTable
| 1 | {1, 2, 3, 4, 5} |
| 2 | {4, 7, 5, 2, 3} |
| 3 | {8, 1}          |
etc

pointTable
| 1 | 1 | 0 |
| 2 | 2 | 1 |
| 3 | 5 | 6 |
| 4 | 7 | 0 |
| 5 | 2 | 4 |
| 6 | 5 | 2 |
| 7 | 4 | 4 |
| 8 | 5 | 9 |

So what I want the eventual output to be would be

lineValues
| {{1, 0}, {2, 1}, {5, 6}, {7, 0}, {2, 4}} |
| {{7, 0}, {4, 4}, {2, 4}, {2, 1}, {5, 6}} |
| {{5, 9}, {1, 0}}                         |

Note: The ID's can be any number and are not in a sequential pattern (it may go 1, 2, 5, 10, 11, 18)

I am using Postgre 9.2.

Thank You for any assistance. Let me know if you need/want any other information.

The best solution is to change your very poor schema. It is really a very bad one. — Clodoaldo Neto, Nov 16 '12 at 23:02
It would be better to provide some SQL statements for re-creating table and data — mys, Nov 16 '12 at 23:47

score 1 · Accepted Answer · answered Nov 16 '12 at 23:40

This is pretty straight forward if you wanted to use a composite type, such as the native Point type, in your array. Something like this:

select dt.id, array_agg(point(p.x, p.y))
from pointtable p
join (
    select id, unnest(points) as p
    from linetable
) as dt on p.id = dt.p
group by dt.id
order by dt.id;

would give you this sort of output:

 id |                 array_agg                 
----+-------------------------------------------
  1 | {"(1,0)","(2,1)","(5,6)","(7,0)","(2,4)"}
  2 | {"(2,4)","(4,4)","(7,0)","(5,6)","(2,1)"}
  3 | {"(1,0)","(5,9)"}

Then you could work with the points.

If you really want a two dimensional array then you can create your own aggregate function:

create aggregate
array_cat_agg(int[])
(sfunc = array_cat, stype = int[], initcond = '{}');

and then:

select dt.id, array_cat_agg(array[[p.x, p.y]])
from pointtable p
join (
    select id, unnest(points) as p
    from linetable
) as dt on p.id = dt.p
group by dt.id
order by dt.id;

to get this:

 id |          array_cat_agg          
----+---------------------------------
  1 | {{1,0},{2,1},{5,6},{7,0},{2,4}}
  2 | {{2,1},{5,6},{7,0},{2,4},{4,4}}
  3 | {{1,0},{5,9}}

Note the array[[...]] trickery, you need that to make sure you get the right version of array_cat, if you just array[...] then you'll end up with a flattened array and that will make you sad. You can't just use array_agg here as there's no such thing as an array-of-arrays and array_agg(array[p.x, p.y]) wants to create an array-of-arrays. Hence the custom aggregate.

Hey Thanks. I was able to apply that to my database. I appreciate the help. — gibetcher, Nov 21 '12 at 16:01

Converting id's in int[] elements in another table Postgres

1 Answers1