CODEWITHSUNDEEP
postgresqljpaspring-data-jpahsqldb
louis amoros
louis amoros

Reputation: 2546

Postgresql to Hsql

I have this postgres table:

CREATE TABLE News (
    tags        text[](10),
    contract    varchar(40),
    ...others
);

I need to get all the distinct tags for a given contract. I found the postgresql request which works well:

SELECT array_agg(acc)
FROM (
       SELECT DISTINCT unnest(tags::text[])
       FROM my_schema.news
       WHERE contract = 'acontract'
     ) AS dt(acc);

As I am using spring data jpa, I try to convert this request into an HSQL request but I can't make it work. Any idea on what the conversion could be?

Upvotes: 5

Views: 1566

Answers (1)

fredt
fredt

Reputation: 24352

In addition to SET DATABASE SQL SYNTAX PGS TRUE you need to reference the arrays according to the SQL standard.

CREATE TABLE News (tags text array[10], contract varchar(40))

Then

select array_agg(acc) from (
   select distinct acc from news, unnest(tags) un(acc)
   WHERE contract = 'acontract'
)

Upvotes: 2

Related Questions