How to do if elif statements in postgressql and save the outcome in a new column?

i’m quite new to postgressql, so i have a table called public.dim_products with a column called weight_kg
what i would like to do is given some weight in the weight ranges below to set up a new column called weight_class and have these values

`+--------------------------+-------------------+
 | weight_class VARCHAR(?)  | weight range(kg)  |
 +--------------------------+-------------------+
 | Light                    | < 2               |
 | Mid_Sized                | 3 - 40            |
 | Heavy                    | 41 - 140          |
 | Truck_Required           | > 141             |
 +----------------------------+-----------------+`

I’ve had a quick look at case statements and it seems to be what i’m after but when i do the statement it comes up with an error

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "CASE"

`SELECT weight_kg FROM public.dim_products
 CASE 
     WHEN weight_kg < 2 THEN Light
     WHEN weight_kg > 2 AND weight_kg < 41 THEN Mid_Sized
     WHEN weight_kg > 40 AND weight_kg <141 THEN HEAVY
     WHEN weight_kg > 140 THEN Truck_Required
 END weight_class;`

Ideally this shoul’ve created a column and had all these new mapping?

Using Kamran suggestion this is the corrected version of the syntax

dim_products_create_weight_class_col = """SELECT weight_kg,
 CASE 
     WHEN weight_kg::float < 2 THEN 'Light'
     WHEN weight_kg::float >= 2 AND weight_kg::float < 41 THEN 'Mid_Sized'
     WHEN weight_kg::float >= 41 AND weight_kg::float <141 THEN 'HEAVY'
     WHEN weight_kg::float >= 141 THEN 'Truck_Required'
 END AS weight_class
 FROM public.dim_products;

The issue is, it still didn’t create the weight_class column?

>Solution :

The syntax of your CASE statement is incorrect. Here’s a corrected version that follows the proper syntax:

SELECT weight_kg, 
       CASE 
           WHEN weight_kg < 2 THEN 'Light'
           WHEN weight_kg >= 2 AND weight_kg < 41 THEN 'Mid_Sized'
           WHEN weight_kg >= 41 AND weight_kg < 141 THEN 'HEAVY'
           WHEN weight_kg >= 141 THEN 'Truck_Required'
       END AS weight_class
FROM public.dim_products;

SELECT statement only retrieves the data. If you want to update an existing column, you may use following:

UPDATE public.dim_products
SET weight_class = 
       CASE 
           WHEN weight_kg < 2 THEN 'Light'
           WHEN weight_kg >= 2 AND weight_kg < 41 THEN 'Mid_Sized'
           WHEN weight_kg >= 41 AND weight_kg < 141 THEN 'HEAVY'
           WHEN weight_kg >= 141 THEN 'Truck_Required'
       END;

If weight_class column doesn’t exist, you can create with:

ALTER TABLE public.dim_products
ADD COLUMN weight_class varchar(20);