Set column of a select query from an array in Postgres [closed]

Posted on

Question :

Is there a way to set a column of a select query from an array? Assuming we have the following table and array:

items table

item
a
b
c
d
e
z

array of ids

ARRAY[3, 5, 11]

How can we run a select query which gives the following result?

item id
a 3
b 5
c 11

Answer :

You could do the following (based on assumptions! The ORDER BY is the key issue here):

  • assumption (for this solution): there has to be a correspondance between your items and your array of ids, (in this case) the first (when sorted alphabetically) record of the items table has to correspond to the first element of the array table (when taken element by element in INSERT order).

    you could, if required, do various things such as, say, remove duplicates in the array or sort the array, but that wasn’t asked.

So, our tables and data (all the code below is available on the fiddle here):

CREATE TABLE items
(
  item TEXT NOT NULL PRIMARY KEY
);

populate the table:

INSERT INTO items VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g');

And the array:

CREATE TABLE id_array
(
  the_arr INT[] 
);

The elements:

INSERT INTO id_array VALUES
(ARRAY[3,5,11]);

Now, we have to establish a JOINing criterion. As outlined above, this will be done by first, sorting the items table alphabetically. I’ve put a PRIMARY KEY on the items table but this isn’t strictly necessary.

SELECT item, ROW_NUMBER() OVER (ORDER BY item ASC)
FROM items
ORDER BY item ASC;

Result:

item    row_number
   a         1
   b         2
   c         3
   d         4
   e         5
   f         6
   g         7

and for the array (sorted by INSERT order):

SELECT
  * 
FROM UNNEST
     (
       (SELECT the_arr FROM id_array)
     ) WITH ORDINALITY AS t (elem, nr);

Result:

elem    nr
   3    1
   5    2
  11    3

So, now we join the two tables:

SELECT 
  tab1.item, tab1.rn,
  tab2.elem, tab2.nr
FROM
(
  SELECT i.item, ROW_NUMBER() OVER (ORDER BY item ASC) AS rn
  FROM items i
  ORDER BY i.item ASC
) AS tab1
JOIN
(
  SELECT
    * 
  FROM UNNEST
       (
         (SELECT the_arr FROM id_array)
       ) WITH ORDINALITY AS t (elem, nr)
) AS tab2
ON tab1.rn  = tab2.nr;

Result:

item    rn  elem    nr
   a     1     3     1
   b     2     5     2
   c     3    11     3

I’ll leave the clearing up of the SQL to you! I found these posts helpful:

Edited: I put an explicit ORDER BY clause in the ROW_NUMBER() function – thanks to @a_horse_with_no_name for pointing out that there could potentially be problems if this was not done!

Leave a Reply

Your email address will not be published.