PostgreSQL/PostGIS - Insert values in a new column with a case statement [closed]

Question

I'm writing a SQL script in PostgreSQL and I need to reclassify certain values to a new column that form a label of the values. For example, the values of column A range from 1 to 10 and I want to add a new column B where the values of column A are represented with a label. Stating that values <3 will get the label 'Low' .

I figured out that I need to work with case statements and this is currently my script, but it does not seem to work.

ALTER TABLE 'MyTable' ADD COLUMN 'Labels' varchar(50)

INSERT INTO 'MyTable' ('Labels)

SELECT 'LabelValues',

CASE WHEN 'LabelValues' > X THEN 'Label 1'

WHEN 'LabelValues' > X AND 'LabelValues' <= X THEN 'Label 2'

WHEN 'LabelValues'> X AND 'LabelValues' <= X THEN 'Label 3'

WHEN 'LabelValues'> X AND 'LabelValues'<= X THEN 'Label 4'

WHEN 'LabelValues' <= X THEN 'Label 5'

END

FROM 'MyTable

Answer 1

There are several problems with your query. First of all you use single quotes which makes query invalid. Postgres will interpret strings within single quotes as literal values (not column names.

Next problem is that you use insert statement. You have one table "MyTable" with two columns "Labels" and "LabelValues". Your query will insert new rows based on the select you performed. I'm guessing that you want to update labels based on label values. X is I guess integer or float value. On top of that your select is incorrect since it is returning two columns and you are inserting one column. Below is the valid version of SQL statement you provided which inserts new rows to the table.

ALTER TABLE "MyTable" ADD COLUMN "Labels" varchar(50)

INSERT INTO "MyTable" ("Labels") (
  SELECT 
    CASE WHEN "LabelValues" > 10 THEN 'Label 1'
      WHEN "LabelValues" > 8 AND "LabelValues" <= 10 THEN 'Label 2'
      WHEN "LabelValues"> 6 AND "LabelValues" <= 8 THEN 'Label 3'
      WHEN "LabelValues"> 4 AND "LabelValues" <= 6 THEN 'Label 4'
      WHEN "LabelValues" <= 4 THEN 'Label 5'
    END
  FROM "MyTable"
);

On the other hand if you want to update rows (labels) based on label values then you can use these SQL statements below:

UPDATE "MyTable" SET "Labels" = 'Label 1' WHERE "LabelValues" > 10;
UPDATE "MyTable" SET "Labels" = 'Label 2' WHERE "LabelValues" > 8 AND "LabelValues" <= 10;
UPDATE "MyTable" SET "Labels" = 'Label 3' WHERE "LabelValues"> 6 AND "LabelValues" <= 8;
UPDATE "MyTable" SET "Labels" = 'Label 4' WHERE "LabelValues"> 4 AND "LabelValues" <= 6;
UPDATE "MyTable" SET "Labels" = 'Label 5' WHERE "LabelValues" <= 4;