Postgres 2d array dynamic length

Question

postgres supports multi dimensional arrays, however the length is restricted.

CREATE TABLE test (
    field1 text[][]
);

INSERT INTO test VALUES ('{{null, null}, {foo, null}, {foo, bar}}');

this works fine, but if I do

INSERT INTO test VALUES ('{{}, {foo}, {foo, bar}}');

this gives an error 'malformed array literal: "{{}, {foo}, {foo, bar}}"'. Can I do something like the above but without errors?

I have a hard time finding justification for de-normalizing using single dimension arrays. The use case for de-normalizing a model using a 2d array seem even smaller. — user330315
– user330315, Commented Aug 26, 2021 at 5:33
We can break it down to another table but the overall structure of the database is complicated enough so a 2d array will be easier for our implementation and maintenance — AARon
– AARon, Commented Aug 26, 2021 at 7:08
e.g. a 2d array representing Intersection/union of groups of elements [[a], [b, c]] -> a ∩ (b ∩ c) — AARon
– AARon, Commented Aug 26, 2021 at 7:15

Laurenz Albe · Accepted Answer · 2021-08-26 04:40:45Z

1

You could use a domain:

CREATE DOMAIN x AS text[];

CREATE TABLE test (field1 x[]);

INSERT INTO test VALUES (ARRAY[
                            ARRAY[]::x,
                            ARRAY['foo']::x,
                            ARRAY['foo', 'bar']::x
                         ]);

TABLE test;

           field1           
════════════════════════════
 {"{}","{foo}","{foo,bar}"}
(1 row)

answered Aug 26, 2021 at 4:40

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

AARon Over a year ago

Is there a way to insert by literal constant? Because I'm using Hasura and it only works with array literal

Laurenz Albe Over a year ago

Sure, use the exact format of the final query result.

Collectives™ on Stack Overflow

Postgres 2d array dynamic length

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related