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);