Unique Indexes query for HSQLDB

Question

I've got a query that selects unique indexes from a MySQL DB table:

SHOW indexes from [tablename] WHERE non_unique = FALSE and key_name <> "PRIMARY"

I am looking to create the equivalent query that works for HSQLDB

Any help is much appreciated!

Answer 1

That's available in information_schema.system_indexinfo

select *
from information_schema.system_indexinfo
where non_unique = false
  and table_name = 'MY_TABLE'
  and table_schem = 'PUBLIC'
  and not exists (select * -- exclude PK indexes
                  from information_schema.system_primarykeys pk
                  where ii.table_cat = pk.table_cat
                    and ii.table_schem = pk.table_schem
                    and ii.index_name = pk.pk_name)