Cannot figure out the issue with this SQL CASE

CASE
WHEN CAST(a.category AS VARCHAR) like '%jumpsuits%'   THEN CAST(a.google_product_category AS VARCHAR) = 'Apparel & Accessories > Clothing > One-Pieces > Jumpsuits & Rompers'
WHEN CAST(a.category AS VARCHAR) like '%knitwear%'    THEN CAST(a.google_product_category AS VARCHAR) = 'Apparel & Accessories > Clothing > Dresses'
WHEN CAST(a.category AS VARCHAR) like '%lounge wear%' THEN CAST(a.google_product_category AS VARCHAR) = 'Apparel & Accessories > Clothing > Shirts & Tops'
ELSE CAST(a.google_product_category AS VARCHAR) END as google_product_category

The issue i am facing is: “SYNTAX_ERROR: line 7:6: All CASE results must be the same type: boolean”

I have also joined the table in a previous sub query casting it as a varchar, so there shouldn’t be any issues with this.

Anyone have any ideas?

For context i am trying to populate a field that would return a blank where the category is like my “likes” anything else i just want it to remain as it is.

Many thanks

Answer

Your CASE has expressions returning different data types, which is not allowed. If you’re simply trying to update the google_product_category based on the category column, try something like this:

Data sample

CREATE TEMPORARY TABLE t (category TEXT, google_product_category TEXT);
INSERT INTO t VALUES ('Jumpsuit'),('kniTweaR'),('lounge WEAR');

Query

SELECT category,
  CASE 
    WHEN category ILIKE '%jumpsuit%' 
      THEN 'Apparel & Accessories > Clothing > One-Pieces > Jumpsuits & Rompers'
    WHEN category ILIKE '%knitwear%' 
      THEN 'Apparel & Accessories > Clothing > Dresses'
    WHEN category ILIKE '%lounge wear%' 
      THEN 'Apparel & Accessories > Clothing > Shirts & Tops'
  END
FROM t;

  category   |                                case                                 
-------------+---------------------------------------------------------------------
 Jumpsuit    | Apparel & Accessories > Clothing > One-Pieces > Jumpsuits & Rompers
 kniTweaR    | Apparel & Accessories > Clothing > Dresses
 lounge WEAR | Apparel & Accessories > Clothing > Shirts & Tops
(3 Zeilen)