[FIXED] Can't create table with text[] data type

Issue

I'm trying to store an entity in my postgresql database. This entity has a List in it, so I'd like to use postgresql type TEXT[]. But everytime I'm trying I get a SQL error, I have no idea why.

I don't get the syntax error, really. I'm sure it's a dumb issue but can you help me?

Thank you

I tried some alternatives, creating it directly from h2 console but I always get the same error

The script I use with flyway for creating the table

CREATE TABLE discrimination(
    id SERIAL PRIMARY KEY NOT NULL ,
    location VARCHAR(255) NOT NULL,
    criteria TEXT[] NOT NULL,
    domain VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    name_organ VARCHAR(55) NOT NULL,
    function_disc VARCHAR(55) NOT NULL
);

my application config for h2 & flyway

h2:
    console:
      enabled: true
      path: /h2
  datasource:
    url: jdbc:h2:mem:formation-iris;MODE=PostgreSQL
    username: test
    password: test
    driver-class-name: org.h2.Driver
  flyway:
    locations: classpath:db/migration
    enabled: true

And the error I get

Syntax error in SQL statement "CREATE TABLE DISCRIMINATION( 
    ID SERIAL PRIMARY KEY NOT NULL , 
    LOCATION VARCHAR(255) NOT NULL, 
    CRITERIA TEXT[[*]] NOT NULL, 
    DOMAIN VARCHAR(255) NOT NULL, 
    DESCRIPTION TEXT NOT NULL, 
    NAME_ORGAN VARCHAR(55) NOT NULL, 
    FUNCTION_DISC VARCHAR(55) NOT NULL 
) "; expected "(, FOR, UNSIGNED, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:

Solution

From H2 documentation:

Compatibility Modes

For certain features, this database can emulate the behavior of specific databases. However, only a small subset of the differences between databases are implemented in this way.

Which means that H2 can emulate certain DB-specific behaviours, but it won't be fully compatible with the selected DB. That's especially true for SQL syntax. So, if you want to use arrays in H2, then you should use the H2 syntax ARRAY instead of TEXT[]

Which also means that you will need a separate SQL script for production (PostgreSQL) and for tests (H2). Luckily, flyway supports that. It can load the vendor-specific scripts from different folders. Extend the flyway configuration this way:

spring.flyway.locations=classpath:db/migration/{vendor}

and add the vendor-specific SQL scripts under the /h2 and /postgresql folders respectively.

Answered By - Selindek
Answer Checked By - David Goodson (JavaFixing Volunteer)