Given the following query:
WITH t as (
SELECT name, array_agg(DISTINCT("age", "gender")) as "ages_and_genders"
FROM (
SELECT * FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender")
) as t
GROUP BY name
)
SELECT name, "ages_and_genders"[1]
FROM t
WHERE array_length("ages_and_genders", 1) = 1
How do I go about breaking apart the record/tuple returned into the "age" and "gender" as separate columns?
I expect to get back a result:
name | age | gender
-------------------
"alice" | 30 | 'f'
2
Answers
Postgres cannot decompose anonymous record types. To access individual fields, the nested structure must be known. Cast to a well-known row type. If no matching type exists, yet, register one first. There are various ways. For ad-hoc use, a "temporary" type (undocumented) is advisable. (For repeated use, register a plain type.)
The manual:
Run once in your session:
Then, with a drop-in fix (preserving other awkward syntax):
The same, consolidated:
Of course, I still wouldn’t use that.
Typically, there are much simpler and faster solutions. Like:
Or:
fiddle
If
age
andgender
areNOT NULL
, you can simplify.Notes
You may not be aware of all the things going on in your code example.
You don’t need
SELECT * FROM (VALUES ...
in this context. TheVALUES
expression can stand on its own.The simple string and numeric constants default to the basic types
text
andinteger
. For other data you may want explicit type declarations.For example, it’s typically better to work with birthdays instead of age. The type
date
would need an explicit cast or declaration in the first input row (or any, really) like:DISTINCT
is not a function. In your query, it’s a syntax element of the aggregate function.And
("age", "gender")
is a ROW constructor (with redundant double-quotes).Clear syntax for both:
array_agg(DISTINCT ROW(age, gender))
.If all values are
NOT NULL
, simpler expressions are possible. My given queries are null-safe.Postgres’ handling of anonymous records is a bit lacking in corner case situations. Not least because few care, as there are almost always better solutions.
For functions returning anonymous records, you can append a column definition list. Not applicable here.
In SQL, anonymous records don’t have named fields in the same way that you might have in a programming language like C# or F#. However, it sounds like you might be referring to accessing fields of a result set from a query.Is it correct?