[GENERAL] how to do merge in postgres ("with upsert as" not supported)

[Tong Michael]

hey, guys, I came across a merge statement when I'm trying to convert
stored procedures from Mysql to Postgres:



merge into db.ChargePeriod d

using (

    select ba.ClientID

         , ba.BillingAccountID

         , bs.BillingScheduleID

         , @CodeWithholdD as WithholdTypeID

      from db.ClientPartyIDConfiguration cpc

      join db.BillingAccount ba

        on ba.Deleted = 0

       and ba.ClientID = cpc.ClientID

       and ba.PartyID = cpc.PartyID

       and convert(date,getdate()) between ba.EffectiveDate and
ba.ExpireDate

      join db.BillingSchedule bs

        on bs.Deleted = 0

       and bs.ClientID = ba.ClientID

       and bs.CoverageBeginDate >= ba.EffectiveDate

       and bs.CoverageBeginDate <= ba.ExpireDate

    where cpc.Deleted = 0

       and cpc.ClientID = @ClientID

) s on d.Deleted = 0

   and d.ClientID = s.ClientID

   and d.BillingAccountID = s.BillingAccountID

   and d.BillingScheduleID = s.BillingScheduleID

  when matched

   and isNull(d.WithholdTypeID,-1) <> isNull(s.WithholdTypeID,-1)

  then update

   set WithholdTypeID = s.WithholdTypeID

     , UpdateUser     = @UpdateUser

     , UpdateDate     = @UpdateDate

  when not matched then insert (

           ClientID

         , BillingAccountID

         , BillingScheduleID

         , WithholdTypeID

         , CreateUser

         , CreateDate

         , Deleted

         , CancelDate

       ) values (

           s.ClientID

         , s.BillingAccountID

         , s.BillingScheduleID

         , s.WithholdTypeID

         , @UpdateUser

         , @UpdateDate

         , 0

         , '9999-12-31'

       )

;



I saw some people use "with upsert as", but my pgAdmin version(1.8) doesn't
support it. Anyone has any ideas how to do merge in postgres?



Thanks.