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?

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

Collectives™ on Stack Overflow

Postgres 2d array dynamic length

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest