HQL 5.6.4 - INSERT an array of strings into VARCHAR[] column

Hibernate ORM
1

My PostgreSQL column is defined as a VARCHAR, and I am trying to insert an array of strings into the table using (an ordinal parameter and) native SQL and HQL. However, special HQL characters - like the left and right brackets - cause parsing problems when I setParameter in the HQL.

What is the proper way to insert an arbitrary list of strings into a VARCHAR column?

INSERT INTO kw.ddoc_all ( textarea_8941776, id ) VALUES ( CAST(?1 AS VARCHAR[]), CAST(?2 AS UUID) );
hql.setParameter(ppIndex, "{" + concatenated + "}" );
2

The PostgreSQL literal syntax which you are trying to make use of here requires that you properly escape the individual values. You have to wrap the content in " and escape " characters within the content by doubling the character.
Or you simply update to Hibernate 6.2 which supports arrays out of the box.

1 Like
3

Thank you for your answer!