CODEWITHSUNDEEP
sqlpostgresql
Jane Borges
Jane Borges

Reputation: 592

How to create a new column in the table that receives the value of a condition using postgreSQL?

I have the following table in the postgres database (the table name is table_example):

       id          data_sensor_analog        data_sensor_dig         qtd    
      1452               255                       0                   1
      2114               255                       1                   2
      2548               144                       0                   3
      8745               144                       1                   4
      7548               144                       0                   5
      8785               144                       1                   6

I need to create a new column in this table.

This column should receive 'LOW' if the 'qty' column is between [1, 2]. You should receive 'MEAN' if the column 'qty' is between [3, 4] and in other cases you should receive 'HIGH'.

I thought about creating a new column in the table as follows:

       ALTER TABLE table_example
       ADD Class_Output  Varchar(10)

This command creates the new column as desired. However, I don't know how I can do to assign the desired values ​​to the new column.

      -- The desired output is:

       id          data_sensor_analog        data_sensor_dig         qtd           Class_Output
      1452               255                       0                   1              LOW
      2114               255                       1                   2              LOW
      2548               144                       0                   3              MEAN
      8745               144                       1                   4              MEAN
      7548               144                       0                   5              HIGH
      8785               144                       1                   6              HIGH

I thought about building something like the code below, but I don't know how I can combine it with the rest.

       IF (qtd >= 1 AND qtd <= 2) THEN
       -- Insert 'LOW'
       class_output = 'LOW';

       [ELSIF (qtd >= 3 AND qtd <= 4) THEN
       -- Insert 'MEAN'
       class_output = 'MEAN';]

       [ELSE
       -- Insert 'HIGH'
       class_output = 'HIGH';]
      END IF;

Upvotes: 0

Views: 37

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would recommend writing the case expression as:

update table_example
    set class_output = (case when qtd <= 2 THEN 'LOW'
                             when qtd <= 4 THEN 'MEAN'
                             else 'HIGH'
                        end);

In other words, you don't need ranges. The first matching condition is used.

However, my advice is to include this as a generated column:

alter table table_example add class_output text
    generated always as
        (case when qtd <= 2 then 'LOW'
              when qtd <= 4 then 'MEAN'
              else 'HIGH'
         end) stored;

Then you don't have to worry about the column ever being out-of-synch with the qtd column.

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

you can use case :

select  * , case when (qtd >= 1 AND qtd <= 2) THEN 'LOW'
                 when (qtd >= 3 AND qtd <= 4) THEN 'MEAN'
            else 'HIGH'
       end as class_output
from table_example

to update :

update table_example
set class_output= case when (qtd >= 1 AND qtd <= 2) THEN 'LOW'
                       when (qtd >= 3 AND qtd <= 4) THEN 'MEAN'
            else 'HIGH'
       end

but generally this is not something you want to add to your table permanently ,since you can always calculate it whenever is needed.

Upvotes: 1

Related Questions