Sample Header Ad - 728x90

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