0

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
2
  • Hi @BERA, I get the error message stating that 'INSERT has more expressons than target columns' . With X I mean an imaginery value for example 1. – BramBroccoli Jun 24 '20 at 11:34
  • Please check my answer. You will probably need to adjust numbers of values since I put arbitrary values. Error is caused by the fact that you have in select two columns and inserting one. – Saša Vranić Jun 24 '20 at 11:35
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;
2
  • Hi Sasa, thank you for the answer. The second option with the update statement works for my question! – BramBroccoli Jun 24 '20 at 12:28
  • I believe you can also do a single update statement with multiple conditions and updates – DPSSpatial_BoycottingGISSE Jun 24 '20 at 21:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.