Getting error in postgres query No operator matches the given name and argument types-CodePudding

Reason Why code is giving error when groupHds is null getting following error, groupHds is list of string and ddi.group_hd is string coloumn

Error -

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea   Hint: No operator matches the given name and argument types. You might need to add explicit type casts.   Position: 586

query -

"SELECT ddo.* FROM dta_delivery_order ddo JOIN dta_current_state dcs ON dcs.entity_id = ddo.id "  
                    " AND dcs.entity_type = 'DeliveryOrder' JOIN dta_order_info_data doid ON doid.entity_id = ddo.customer_order_id "  
                    " AND doid.entity_type = 'CustomerOrder' JOIN dta_do_item ddi ON ddo.id = ddi.order_id "  
                    " WHERE dcs.process_state_code = 'Delivery_ReceiveTask_WaitForContinueAfterDailyTrigger'"  
                    " AND dcs.create_date BETWEEN CAST(CAST(:dateFrom AS TEXT) AS TIMESTAMP) AND CAST(CAST(:dateTo AS TEXT) AS TIMESTAMP) "  
                    " AND (:channelCode is null OR doid.channel_code = CAST(:channelCode AS TEXT)) "   
                    " AND  (COALESCE(:groupHds) IS NULL OR ddi.group_hd IN (:groupHds) )";

CodePudding user response：

assuming that the bytea column is a text, try to encode the bytea column to text using encode function like this:

encode(your_table.bytea_column_name, 'escape')

reference: PostgreSQL Documentation: Binary String Functions and Operators

CodePudding user response：

Remove .* and try this :

"SELECT ddo FROM dta_delivery_order ddo JOIN dta_current_state dcs ON dcs.entity_id = ddo.id "  
                " AND dcs.entity_type = 'DeliveryOrder' JOIN dta_order_info_data doid ON doid.entity_id = ddo.customer_order_id "  
                " AND doid.entity_type = 'CustomerOrder' JOIN dta_do_item ddi ON ddo.id = ddi.order_id "  
                " WHERE dcs.process_state_code = 'Delivery_ReceiveTask_WaitForContinueAfterDailyTrigger'"  
                " AND dcs.create_date BETWEEN CAST(CAST(:dateFrom AS TEXT) AS TIMESTAMP) AND CAST(CAST(:dateTo AS TEXT) AS TIMESTAMP) "  
                " AND (:channelCode is null OR doid.channel_code = CAST(:channelCode AS TEXT)) "   
                " AND  (COALESCE(:groupHds) IS NULL OR ddi.group_hd IN (:groupHds) )";