Reports

Yann39 mentioned, "JPA's default parameter binding does not always automatically convert to the right type." Then, what could be a way to find the corresponding type in PostgreSQL?

For a working example, I ran SQL statements.

customdevicesdb=# SELECT * FROM devices_resource_tree;
 id |      name      |    path
----+----------------+------------
  1 | Device Arthur  | root.P
  2 | Device Billy   | root.P.B
  3 | Device Gabriel | root.P.C.D
  4 | Device Louise  | root.Q.E

I tried:

    @Query(value = "SELECT CAST(pg_typeof(?1) AS text)", nativeQuery = true)
    String findPgTypeofBy(@Param("valOfADataType") Integer valOfADataType); 

    @Query(value = "SELECT CAST(pg_typeof(?1) AS text)", nativeQuery = true)
    String findPgTypeofBy(@Param("valOfADataType") Integer[] valOfADataType); 

    @Query(value = "SELECT CAST(pg_typeof(?1) AS text)", nativeQuery = true)
    String findPgTypeofBy(@Param("valOfADataType") Double valOfADataType); 

    @Query(value = "SELECT CAST(pg_typeof(?1) AS text)", nativeQuery = true)
    String findPgTypeofBy(@Param("valOfADataType") String valOfADataType); 

    @Query(value = "SELECT CAST(pg_typeof(?1) AS text)", nativeQuery = true)
    String findPgTypeofBy(@Param("valOfADataType") List<String> valOfADataType);   

    @Query(value = "SELECT CAST(pg_typeof(?1) AS text)", nativeQuery = true)
    String findPgTypeofBy(@Param("valOfADataType") String[] valOfADataType);

SELECT pg_typeof(?1) did not work for me. SELECT CAST(pg_typeof(?1) AS text) worked for me.

I got:

What is the corresponding data type in PostgreSQL for some parameters in methods in JpaRepository?
Hibernate: SELECT CAST(pg_typeof(?) AS text)
The corresponding data type of Integer.valueOf(1): integer
Hibernate: SELECT CAST(pg_typeof(?) AS text)
The corresponding data type of new Integer[]{5, 7, 9}: integer[]
Hibernate: SELECT CAST(pg_typeof(?) AS text)
The corresponding data type of Double.valueOf(0.3): double precision
Hibernate: SELECT CAST(pg_typeof(?) AS text)
The corresponding data type of new String("some text"): character varying
Hibernate: SELECT CAST(pg_typeof(?) AS text)
The corresponding data type of List.of("root.P"): character varying
Hibernate: SELECT CAST(pg_typeof(?) AS text)
The corresponding data type of new String[]{"root.P"}: character varying[]

I tried:

    @Query(value = "SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY(CAST(?1 AS ltree[]))", nativeQuery = true)
    List<DeviceEntity> findByPathContaining(@Param("paths") String[] paths);

I got:

DeviceManagementService.findDeviceEntity(new String[]{"root.P"})
deviceRepository.findByPathContaining(paths)
Hibernate: SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY(CAST(? AS ltree[]))
1_Device Arthur_root.P
2_Device Billy_root.P.B
3_Device Gabriel_root.P.C.D

The error was gone.

My example repository was here.

The text data type is a variable-length character string.

An array data type is named by appending square brackets ([]) to the data type name of the array elements.

To verify,

customdevicesdb=# SELECT pg_typeof(ARRAY['root.m.n.o', 'root.p.q.r']);
 pg_typeof
-----------
 text[]

ARRAY['root.m.n.o', 'root.p.q.r'] is an array of text.

customdevicesdb=# SELECT CAST(ARRAY['root.m.n.o', 'root.p.q.r'] AS ltree[]);
          array
-------------------------
 {root.m.n.o,root.p.q.r}

customdevicesdb=# EXPLAIN ANALYZE SELECT * FROM devices_resource_tree WHERE path <@ ANY(CAST(ARRAY['root.m.n.o', 'root.p.q.r'] AS ltree[]));
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on devices_resource_tree  (cost=4.41..14.62 rows=17 width=68) (actual time=0.042..0.042 rows=0 loops=1)
   Recheck Cond: (path <@ ANY ('{root.m.n.o,root.p.q.r}'::ltree[]))
   ->  Bitmap Index Scan on idx_devices_resource_tree_path  (cost=0.00..4.41 rows=17 width=0) (actual time=0.026..0.026 rows=0 loops=1)
         Index Cond: (path <@ ANY ('{root.m.n.o,root.p.q.r}'::ltree[]))
 Planning Time: 0.234 ms
 Execution Time: 0.081 ms

79604299