Обсуждение: how to do merge in postgres ("with upsert as" not supported)
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.
On 2/26/15 2:23 PM, Tong Michael wrote: > 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?____ Actually, this feature is in active development and will hopefully make it into 9.5. In the meantime, take a look at Example 40-2 in the Trapping Errors section of the plpgsql docs for how you can do this today: http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
Tong Michael schrieb am 26.02.2015 um 21:23: > > 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____ > ... > ... That can't be MySQL - MySQL does not have a MERGE statement. That looks much more like SQL Server's T-SQL (MERGE, @ style variables, convert() function...) Here are several ways to do it: http://stackoverflow.com/q/1109061/330315 > I saw some people use "with upsert as", but my pgAdmin version(1.8) doesn't support it. pgAdmin supports whatever your Postgres version supports. All supported/maintained Postgres versions support common table expressions. What exactly do you mean with "doesn't support it"? What was the exact SQL statement you tried?