1

Here is the sample database schema.

| id |  Critical     |  High  |    Low     |
--------------------------------------------
| 1  | {apple, ball} |  {cat} | {dog, egg} |

Now I want to remap these to another table. Critical set to 1, High to 2 and Low to 3 like:

| element | priority |
----------------------
|  apple  |   1      |
|  ball   |   1      |
|  cat    |   2      |
|  dog    |   3      |
|  egg    |   3      |

1,2,3 can be changed to column name too.

I've written a query to do it for 1 column at a time:

insert into new_table (element, priority)
select unnest(critical), 1 from old_table where id=1;

But I want to do all the 3 columns in this single query.

I want it fast performing. Is UNION the most efficient way?

4
  • 1
    is UNION the most efficient way? UNION ALL seems to be the only way. Especially when some particular value may be a part of array in any column. PS. Your JSON is strange. {} means this is an object but the value looks like an array which must be enclosed with [].
    – Akina
    Commented Mar 15, 2021 at 5:47
  • Sorry. Its actually array. I represented the way postgresql does
    – PaxPrz
    Commented Mar 15, 2021 at 5:55
  • Still, UNION ALL is the way to go. Commented Mar 15, 2021 at 13:12
  • Like I commented on another question of yours, why are you using arrays to store the data rather than a true normalized relational model? Commented Mar 16, 2021 at 15:42

1 Answer 1

0

UNION ALL, not UNION:

SELECT id, unnest(critical) AS element, 1 AS prio FROM tbl
UNION ALL SELECT id, unnest(high)     , 2         FROM tbl
UNION ALL SELECT id, unnest(low)      , 3         FROM tbl;

Optionally drop the id (like in your displayed result), but I suspect you need it.

For big tables, a LATERAL subquery may be faster as it only uses a single sequential scan:

SELECT sub.*
FROM   tbl t
CROSS  JOIN LATERAL (
      SELECT id, unnest(t.critical) AS element, 1 AS prio
   UNION ALL SELECT id, unnest(t.high)        , 2
   UNION ALL SELECT id, unnest(t.low)         , 3
   ) sub;

db<>fiddle here

Otherwise, this question seems like a subset of your later more complex question:

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.