Postgresql conditional script

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

Answer

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