How to turn a variable-length record in postgres into an array?

Question

I'm trying to dynamically create tables and turn their columns into arrays. So I would end up with a table that has just 1 column, of type array. The lenght of the array would be equal to the amount of columns in the input. If I knew in advance which columns my (dynamically created) table has, the solution would be simple, but I don't know this.

I've tried using the array function. This works if you know in advance which columns your table contains. If you don't know this (because the table is dynamically defined) you have to use an asterisk to get all columns. But the array function then creates an array with just 1 element, containing all columns. This is not what I want.

CREATE TEMPORARY TABLE my_addresses(
  personname TEXT NOT NULL, 
  streetname TEXT NOT NULL, 
  country_id TEXT
);
------------------------------------------------------------------------
INSERT INTO my_addresses 
VALUES(  'Bill Gates', 'Somewhere Avenue', 'US'),
        ('Linus Thorvalds', 'Other Road', 'US'),
        ('Erwin Brandstetter', 'Solution Street', 'AT'); --it's a tribute :-)
------------------------------------------------------------------------
SELECT 
    cardinality(ARRAY[  ma.personname, 
                        ma.streetname, 
                        ma.country_id]), --an array of 3 elements is created, as you would expect
    cardinality(ARRAY[ma.*]) --an array of 1 element is created, which is not what I want
FROM my_addresses   AS ma;

Btw, this is my first post ever on stackoverflow (forgive me any formatting errors). I use Postgres version 9.6.14.

Gordon Linoff · Accepted Answer · 2019-08-26 16:42:02Z

3

This seems curious. But as with many challenges that involve entire records, the solution often is to use JSON. In this case:

SELECT (SELECT array_agg(t.v)
        FROM jsonb_each_text(to_jsonb(ma.*)) t(k, v)
       ) as myarray
FROM my_addresses ma;

Here is a db<>fiddle.

You may find that JSON is a better solution for your ultimate problem anyway. But this question asks about arrays in particular.

answered Aug 26, 2019 at 16:42

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Paul de Roos Over a year ago

While this is excellent stuff, is is not exacty what I was looking for. The reason I asked specifically for an array solution is that I want the order to be maintained; so "{"Bill Gates","Somewhere Avenue", "US"}, not {"US", "Bill Gates","Somewhere Avenue"}. I guess I should have specified that. Point of curiosity; why are the parentheses in the country_code removed in your solution (i.e. I get this output: "{US,"Bill Gates","Somewhere Avenue"}", "{US,"Linus Thorvalds","Other Road"}", "{AT,"Erwin Brandstetter","Solution Street"}" )?

Gordon Linoff Over a year ago

@PauldeRoos . . . (1) If you want the results in a particular order, you can add an order by to the array_agg(). Ironically (perhaps) changing "jsonb" to seems to preserve the original ordering. (2) I have no idea what you are referring to with the reference to parentheses. There is no "country_code" and no parentheses in the data.

Paul de Roos Over a year ago

I meant country_id, not country_code. And you're right, there is no parentheses in the output, I meant quotation marks (it was still early when I responded). Concerning the order, I meant to preserve the original order, but come to think of it because in my real-world problem the columns are actually numbered, so I can actually use that to get the order right. Which means your answer works for me :-)

Collectives™ on Stack Overflow

How to turn a variable-length record in postgres into an array?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related