Operator does not exist: text = boolean

💬 Queries and Resources
1

I am using the following SQL statement:

UPDATE
  shipping
SET
  load_time_misc = CASE
    WHEN {{ cell_update.value }} = 'load_time' THEN
      CASE
        WHEN load_time_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  trailer_misc = CASE
    WHEN {{ cell_update.value }} = 'trailer' THEN
      CASE
        WHEN trailer_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  status_misc = CASE
    WHEN {{ cell_update.value }} = 'status' THEN
      CASE
        WHEN status_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  ship_date_misc = CASE
    WHEN {{ cell_update.value }} = 'ship_date' THEN
      CASE
        WHEN ship_date_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  del_add_misc = CASE
    WHEN {{ cell_update.value }} = 'del_add' THEN
      CASE
        WHEN del_add_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  contact_misc = CASE
    WHEN {{ cell_update.value }} = 'contact' THEN
      CASE
        WHEN contact_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  del_con_misc = CASE
    WHEN {{ cell_update.value }} = 'del_con' THEN
      CASE
        WHEN del_con_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  driver_misc = CASE
    WHEN {{ cell_update.value }} = 'driver' THEN
      CASE
        WHEN driver_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  model_misc = CASE
    WHEN {{ cell_update.value }} = 'model' THEN
      CASE
        WHEN model_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  notes_misc = CASE
    WHEN {{ cell_update.value }} = 'shipping_notes' THEN
      CASE
        WHEN notes_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END
WHERE
  id = {{ table1.selectedRow.id }}

For some reason, when it runs I keep getting the error message "Operator does not exist: text = boolean". For the life, I cannot figure this out. Any help would be most appreciated.

2

Hey Tom,

The error message "operator does not exist: text = boolean" indicates that there's a type mismatch in your SQL statement: an attempt to compare a text value with a boolean. In PostgreSQL, direct comparisons between different data types, such as text and boolean, aren't allowed without explicit casting.

If load_time_misc, trailer_misc, etc., are boolean columns, then SQL is interpreting the CASE conditions like this:

WHEN load_time_misc = TRUE THEN FALSE ELSE TRUE

This works fine. However, when you do:

WHEN {{ cell_update.value }} = 'load_time'

PostgreSQL checks what type load_time_misc is (which could be boolean), and then tries to compare it to {{ cell_update.value }} (a string). Since you can't compare boolean to text directly in PostgreSQL, you get the "operator does not exist: text = boolean" error.

To resolve this, ensure that {{ cell_update.value }} is treated as a text value. You can achieve this by explicitly casting it to text in your SQL statement. Here's how you can modify your query:

UPDATE
  shipping
SET
  load_time_misc = CASE
    WHEN {{ cell_update.value }}::text = 'load_time' THEN
      CASE
        WHEN load_time_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  trailer_misc = CASE
    WHEN {{ cell_update.value }}::text = 'trailer' THEN
      CASE
        WHEN trailer_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  status_misc = CASE
    WHEN {{ cell_update.value }}::text = 'status' THEN
      CASE
        WHEN status_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  ship_date_misc = CASE
    WHEN {{ cell_update.value }}::text = 'ship_date' THEN
      CASE
        WHEN ship_date_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  del_add_misc = CASE
    WHEN {{ cell_update.value }}::text = 'del_add' THEN
      CASE
        WHEN del_add_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  contact_misc = CASE
    WHEN {{ cell_update.value }}::text = 'contact' THEN
      CASE
        WHEN contact_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  del_con_misc = CASE
    WHEN {{ cell_update.value }}::text = 'del_con' THEN
      CASE
        WHEN del_con_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  driver_misc = CASE
    WHEN {{ cell_update.value }}::text = 'driver' THEN
      CASE
        WHEN driver_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  model_misc = CASE
    WHEN {{ cell_update.value }}::text = 'model' THEN
      CASE
        WHEN model_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END,
  notes_misc = CASE
    WHEN {{ cell_update.value }}::text = 'shipping_notes' THEN
      CASE
        WHEN notes_misc = TRUE THEN FALSE
        ELSE TRUE
      END
  END
WHERE
  id = {{ table1.selectedRow.id }}

That way, PostgreSQL sees the comparison as text = text rather than text = boolean.

1 Like
3

I'm still getting the exact same error.

4

Hey @tomm would you mind sharing a screenshot of your schema for the shipping table? As well as a screen shots of the example query call? What is the value of the input you're using load_time_misc, trailer_misc, status_misc, ship_date_misc, del_add_misc, contact_misc, del_con_misc, driver_misc, model_misc, and notes_misc etc?

It seems like there's an issue with the column types and the types of the actual values being passed into the query. Try checking each column type with the actually value individually.