WITH Recursive query is not working when run using entityManager native query-CodePudding

I'm running this PostgreSQL query on Java and it is throwing an error "ERROR: syntax error at or near ":". But the query is working on Postgresql when I run directly. I'm thinking Array[]::integer[] is causing the issue. Can someone has any idea?

String query = "WITH RECURSIVE tree AS ( SELECT id, ARRAY[]::integer[] AS ancestors \n"  
                "  FROM regions \n"  
                "  WHERE parent_id IS NULL\n"  
                "  UNION ALL \n"  
                "  SELECT soato.id, tree.ancestors || regions.parent_id \n"  
                "  FROM regions, tree \n"  
                "  WHERE regions.parent_id = tree.id \n"  
                ") \n"  
                "  SELECT d.id FROM department d \n"  
                "  WHERE d.region_id IN (select id from tree where 1703 = ANY(tree.ancestors))";
Query q = entityManager.createNativeQuery(query);
q.getResultList();

CodePudding user response：

Use an explicit cast to avoid the implicit PostgreSQL option :: for casting.

ARRAY[CAST(NULL AS INTEGER)] AS ancestors