Question 114330 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Getting error in postgres query No operator matches the given name and argument types

Reason why code is causing error when groupHds is null? I’m getting
this error, groupHds is list of string and ddi.group_hd is string column.

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) )";
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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) )";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search