Casting rows to arrays in PostgreSQL

Question

I need to query a table as in

SELECT *
FROM table_schema.table_name

only each row needs to be a TEXT[] with array values corresponding to column values casted to TEXT coming in the same order as in SELECT * so assuming the table has columns a, b and c I need the result to look like

SELECT ARRAY[a::TEXT, b::TEXT, c::TEXT]
FROM table_schema.table_name

only it shouldn't explicitly list columns by name. Ideally it should look like

SELECT as_text_array(a)
FROM table_schema.table_name AS a

The best I came up with looks ugly and relies on "hstore" extension

WITH columnz AS ( -- get ordered column name array
    SELECT array_agg(attname::TEXT ORDER BY attnum) AS column_name_array
    FROM pg_attribute
    WHERE attrelid = 'table_schema.table_name'::regclass AND attnum > 0 AND NOT attisdropped
)
SELECT hstore(a)->(SELECT column_name_array FROM columnz)
FROM table_schema.table_name AS a

I am having a feeling there must be a simpler way to achieve that

UPDATE 1

Another query that achieves the same result but arguably as ugly and inefficient as the first one is inspired by the answer by @bspates. It may be even less efficient but doesn't rely on extensions

SELECT r.text_array
FROM table_schema.table_name AS a
    INNER JOIN LATERAL ( -- parse ROW::TEXT presentation of a row
        SELECT array_agg(COALESCE(replace(val[1], '""', '"'), NULLIF(val[2], ''))) AS text_array
        FROM regexp_matches(a::text, -- parse double-quoted and simple values separated by commas
            '(?<=\A\(|,)  (?:  "(  (?:[^"]|"")*  )"  |  ([^,"]*)  )  (?=,|\)\Z)', 'xg') AS t(val)
    ) AS r ON TRUE

It is still far from ideal

UPDATE 2

I tested all 3 options existing at the moment

Using JSON. It doesn't rely on any extensions, it is short to write, easy to understand and the speed is ok.
Using hstore. This alternative is the fastest (>10 times faster than JSON approach on a 100K dataset) but requires an extension. hstore in general is very handy extension to have through.
Using regex to parse TEXT presentation of a ROW. This option is really slow.

If you don't list the columns by name, you can't be sure of their order in the resulting array. — Schwern
– Schwern, Commented Sep 3, 2019 at 18:17
@Schwern Of course you can :) My ugly example already does that. The awkward alternative approach by bspates below can achieve that as well :) — oᴉɹǝɥɔ
– oᴉɹǝɥɔ, Commented Sep 3, 2019 at 20:21
That uses the order they're defined in the table. If that changes the order in the array also changes. Having so many columns you don't want to type them out often indicates a schema redesign is needed. — Schwern
– Schwern, Commented Sep 3, 2019 at 21:11
@Schwern it has to do with handling of the result from an arbitrary query in a unified way and not with being lazy to type column names :D — oᴉɹǝɥɔ
– oᴉɹǝɥɔ, Commented Sep 3, 2019 at 21:16
Why don't you just use to_jsonb() to convert the row into a single value? Then you also get the column names as well regardless of the query — user330315
– user330315, Commented Sep 3, 2019 at 21:18

score 3 · Accepted Answer · 2019-09-03 21:14:18Z

3

A somewhat ugly hack is to convert the row to a JSON value, then unnest the values and aggregate it back to an array:

select array(select (json_each_text(to_json(t))).value) as row_value
from some_table t

Which is to some extent the same as your hstore hack.

If the order of the columns is important, then using json and with ordinality can be used to keep that:

select array(select val 
             from json_each_text(to_json(t)) with ordinality as t(k,val,idx) 
             order by idx)
from the_table t

edited Sep 3, 2019 at 21:14

answered Sep 3, 2019 at 20:54

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

bspates Over a year ago

json key/values have no intrinsic order so you would not be able to guarantee that it would be the same order as SELECT *

user330315 Over a year ago

@bspates: when using json instead, the order of the columns can be preserved if that is important

oᴉɹǝɥɔ Over a year ago

Accepting it as the answer though this is not exactly an answer I hoped it would be but unlike mine it doesn't rely on an extension. I thought a row to array conversion could be more straightforward :)

bspates · Accepted Answer · 2019-09-03 19:10:31Z

0

The easiest (read hacky-est) way I can think of is convert to a string first then parse that string into an array. Like so:

SELECT string_to_array(table_name::text, ',') FROM table_name

BUT depending on the size and type of the data in the table, this could perform very badly.

answered Sep 3, 2019 at 19:10

bspates

4122 silver badges10 bronze badges

1 Comment

oᴉɹǝɥɔ Over a year ago

This could work but only when there are no commas in values.

Collectives™ on Stack Overflow

Casting rows to arrays in PostgreSQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related