Re: Is there a better way to unnest an entire row?

Поиск
Список
Период
Сортировка
От David Johnston
Тема Re: Is there a better way to unnest an entire row?
Дата
Msg-id 00ff01cc52f9$8802a1f0$9807e5d0$@yahoo.com
обсуждение исходный текст
Ответ на Is there a better way to unnest an entire row?  ("David Johnston" <polobo@yahoo.com>)
Список pgsql-general
Дерево обсуждения

 

From: Michael Black [mailto:michaelblack75052@hotmail.com]
Sent: Thursday, August 04, 2011 6:42 PM
To: polobo@yahoo.com
Subject: RE: [GENERAL] Is there a better way to unnest an entire row?

 

I do not see where you would need to do a self-join on the table.  However, a union would work nice to accomplish your desired goal.
However, I would think about normalizing the table at some point in the future.

select id, item_name, item_value from
(
select id, item_name as item1_name, item1_value as item_value
union
select id, item_name as item2_name, item2_value as item_value
)
order by id

This has not be tested.

 

FWIW the table is generated from an external source that itself is de-normalized AND from which I can only import the data using CSV.  The reason I am asking the question and writing the query is because “I AM NORMALIZING THE DATA”,  Whether I leave it in a VIEW or materialize it into a table is irrelevant (other than performance concerns).

 

Having written the ARRAY version of the query, and while pondering the UNION version, my instinct says that the UNION version would likely perform better.

 

Either way, and no offense, but I was hoping for some outside-the-box ideas as opposed to the obvious UNION implementation you suggested (which is why I mentioned that I could do the UNION version in my original e-mail).

 

Thanks,

 

David J.

 

 

В списке pgsql-general по дате отправления:

Предыдущее
От: "David Johnston"
Дата:
Сообщение: Is there a better way to unnest an entire row?
Следующее
От: Vivekkumar Pandey
Дата:
Сообщение: postgres table have a large number of relpages and occupied a big memory size