Обсуждение: Is there a better way to unnest an entire row?
Currently I have a de-normalized table with two sets of “records” embedded (i.e., [id, item1_name, item1_amount, item2_name, item2_amount]). My goal is to output two records (i.e., [id, item_name, item_amount]) into an intermediate result and then remove any records where item_name IS NULL or “blank”. There are many possible solutions but I am hoping to solicit some fairly succinct (syntax-wise) possibilities.
I can readily do this using self-joins and UNION constructs but I was to basically trying to write a query that will only access each record once. My gut says that ARRAYS are going to be part of the solution so I tried this:
SELECT unnest(arr_id), unnest(arr_name), unnest(arr_value)
FROM (
SELECT ARRAY[id, id] AS arr_id,
ARRAY[item1_name, item2_name] AS arr_name,
ARRAY[item1_value, item2_value] AS arr_value
FROM table
) arrayed;
It appears you cannot “unnest” a record type so I need an unnest(…) call for each ARRAY I build in the sub-query.
I am currently deploying 9.0 but solutions that are only possible on 9.1 are welcomed as well.
For my current situation this query form will work just fine but I am curious about what others would do with this. Also, I can ensure that each ARRAY in the sub-query only has the same number elements – it seems that using “unnest()” in situations where the ARRAY sizes could vary would be problematic but am I relying upon behavior of “unnest” that I should not be?
Thanks,
David J.
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.
On Thu, Aug 4, 2011 at 5:23 PM, David Johnston <polobo@yahoo.com> wrote: > Currently I have a de-normalized table with two sets of “records” embedded > (i.e., [id, item1_name, item1_amount, item2_name, item2_amount]). My goal > is to output two records (i.e., [id, item_name, item_amount]) into an > intermediate result and then remove any records where item_name IS NULL or > “blank”. There are many possible solutions but I am hoping to solicit some > fairly succinct (syntax-wise) possibilities. > > > > I can readily do this using self-joins and UNION constructs but I was to > basically trying to write a query that will only access each record once. > My gut says that ARRAYS are going to be part of the solution so I tried > this: > > > > SELECT unnest(arr_id), unnest(arr_name), unnest(arr_value) > > FROM ( > > SELECT ARRAY[id, id] AS arr_id, > > ARRAY[item1_name, item2_name] AS arr_name, > > ARRAY[item1_value, item2_value] AS arr_value > > FROM table > > ) arrayed; > > > > It appears you cannot “unnest” a record type so I need an unnest(…) call for > each ARRAY I build in the sub-query. sure you can. it just can't be an anonymous type. postgres=# create type foo_t as (a int, b text); CREATE TYPE postgres=# select unnest(array[(1, 'abc'), row(2, 'def')]); unnest --------- (1,abc) (2,def) postgres=# select (unnest(array[(1, 'abc'), row(2, 'def')])).*; ERROR: record type has not been registered postgres=# select (unnest(array[(1, 'abc'), row(2, 'def')]::foo_t[])).*; a | b ---+----- 1 | abc 2 | def (2 rows) I think that's the 'right' way to do it and if so it makes your question moot. merlin