Postgresql conditional script-CodePudding

I need to update the column (vendor_type) in the table depending on values in other columns.

Table name = "vendor"

Columns = "client_id"(varchar), "coach_id"(varchar), "exm_vendor"(boolean), "vendor_type"(varchar)

And this is what I want to do with postgresql:

if (client_id != null)
    vendor_type = INTERNAL
else if (coach_id != null)
    vendor_type = VENDOR_COACH
else if (exm_vendor == true)
    vendor_type = EXM
else 
    vendor_type = EXTERNAL

CodePudding user response：

Postgresql supports a number of ways to express conditional values, but perhaps the closest to your example is the CASE..WHEN expression

https://www.postgresql.org/docs/14/functions-conditional.html

you can put this into an update statement like this

UPDATE vendor
  SET vendor_type = ( CASE 
                      WHEN client_id IS NOT NULL 
                           THEN 'INTERNAL'
                      WHEN coach_id IS NOT NULL 
                           THEN 'VENDOR_COACH'
                      WHEN exm_vendor 
                           THEN 'EXM' 
                      ELSE
                           'EXTERNAL'
                      END ) ;

CodePudding user response：

You can write your query like the following.

UPDATE v
SET v.vendor_type = (CASE 
        WHEN client_id IS NULL
            THEN INTERNAL
        WHEN coach_id <> NULL
            THEN VENDOR_COACH
        WHEN exm_vendor = 1
            THEN EXM
        ELSE EXTERNAL
        END)
FROM vendor v

CodePudding user response：

Postgres have case...when to use with your condition. This is example script:

update vendor
set vendor_type = (case when client_id is not null then INTERNAL
                       when coach_id is not null then VENDOR_COACH
                       when exm_vendor = true then EXM
                       else EXTERNAL
                  end);

You can readmore at https://www.postgresqltutorial.com/postgresql-case/.