Remap array columns to single column with value
1
vote
1
answer
850
views
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?
Asked by PaxPrz
(219 rep)
Mar 15, 2021, 04:57 AM
Last activity: Jul 30, 2025, 12:01 PM
Last activity: Jul 30, 2025, 12:01 PM