0

In PostgreSQL, say I have a table with a column of 2-dimensional array of integers, and another table storing a column of an array of size 2.

CREATE TABLE ta (arr int[][]);
CREATE TABLE tb (pos int[]);

I want to look up an element in 'arr' based on a value found for 'pos'. I've worked out that I can do it this way:

SELECT arr[(SELECT pos[1] FROM tb WHERE ... LIMIT 1)][(SELECT pos[2] FROM tb WHERE ... LIMIT 1)] FROM ta;

However, that requires repeating the same subquery twice. Is there a way to achieve this without having to repeat the subquery for every dimension of 'arr'?

(Context: I have a board with x and y coordinates given by 'arr', and need to access a value stored in that board based on the position of a player or object, given by 'pos'. Any suggestions on a better schema for this are also okay, especially if what I'm asking for above isn't possible.)

Improve this question
4
  • Are those the only columns in those tables? How do they relate to each other? Any columns on which can join? If each arr has a pos why not put them into a single table? Commented Sep 28, 2014 at 10:19
  • They're not the only columns, and they have ways to join on each other. I wanted to cut the cruft out of the tables to focus on the array access question. Commented Sep 28, 2014 at 10:20
  • 2
    Then please post the full table structure, some sample data and the expected output. Commented Sep 28, 2014 at 10:38
  • 1
    i am researching the same problem, and comments like 'post the full table structure' are not helpful. The problem is clearly stated. Commented Jan 22, 2016 at 19:33
Related questions
2
Getting value from array in a SELECT statement
14
postgresql: select returning ARRAY
8
PostgreSQL - SELECT INTO ARRAY
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.