create domain text[]

[Peter Borissow]

Hello,

    I am using H2 v2.2.224 in PostgreSQL mode. I would like to create a table in H2 with a "text" and "text[]" column. For example, in PostgreSQL I have a table like this:

CREATE TABLE COMPANY (
    ID BIGSERIAL NOT NULL,
    NAME text NOT NULL,
    DESCRIPTION text,
    NAICS text[]
);

I can successfully create a "text" column if I create a custom domain like this:

"CREATE domain IF NOT EXISTS text AS varchar"

However, if I try to create a domain for "text[]" I get an error:

"CREATE domain IF NOT EXISTS text[] AS varchar ARRAY"

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE domain IF NOT EXISTS text[*][] AS varchar ARRAY"; expected "., AS, data type";

I think the syntax error lays in the brackets because if I replace "text[]" with "blah" it works:

"CREATE domain IF NOT EXISTS blah AS varchar ARRAY"

Any suggestions for how to get this to work? Maybe a different syntax for the "create domain" statement or a property of some sort?

Thanks in advance,

Peter

[Evgenij Ryazanov]

Hello!

It isn't going to work, text[] is not an identifier and it cannot be used as domain name.

PostgreSQL supports standard arrays for more than 20 years, just use a standard definition in both PostgreSQL and H2.

[Peter Borissow]

Thanks for confirming that there is currently no way to create a "text[]" domain.

Just as an aside, PostgreSQL has supported "text[]" and other arrays using "column_name datatype []" syntax since at least the 7.x release which is also more than 20 years old ;-)

https://www.postgresql.org/docs/7.1/arrays.html

https://www.postgresql.org/support/versioning/

Thanks again for all your help. H2 is awesome!

Best,

Peter