Thread: BUG #13808: Upsert not working
The following bug has been logged on the website: Bug reference: 13808 Logged by: TARUN Email address: tarunt@chisquare.in PostgreSQL version: 9.5beta1 Operating system: Windows 7 Professional SP1 Description: drop table daily_deposits; create table daily_deposits ( id int, fdd timestamp, ldd timestamp, fda double precision, lda double precision ); insert into daily_deposits (id, fdd, ldd, fda, lda) values (1,'2015-12-01 08:10:50','2015-12-01 10:10:50', 10, 9); insert into daily_deposits (id, fdd, ldd, fda, lda) values (1,'2015-12-02 10:10:50','2015-12-02 12:10:50', 10, 9); insert into daily_deposits (id, fdd, ldd, fda, lda) values (1,'2015-12-04 04:10:50','2015-12-04 08:10:50', 15, 20); insert into daily_deposits (id, fdd, ldd, fda, lda) values (2,'2015-12-01 08:10:50','2015-12-01 10:10:50', 5, 10); insert into daily_deposits (id, fdd, ldd, fda, lda) values (2,'2015-12-02 10:10:50','2015-12-02 12:10:50', 6, 12); insert into daily_deposits (id, fdd, ldd, fda, lda) values (2,'2015-12-03 04:10:50','2015-12-04 08:10:50', 9, 11); commit; select * from daily_deposits; drop table user_first_last; create table user_first_last ( UserId int, FirstLoginDate timestamp, LastLoginDate timestamp, FirstLoginAmt double precision, LastLoginAmt double precision ); INSERT INTO user_first_last AS ufl (UserId, FirstLoginDate,LastLoginDate,FirstLoginAmt,LastLoginAmt) select id, fdd, ldd,fda, lda from daily_deposits ON CONFLICT (UserId) DO UPDATE SET FirstLoginAmt = case when excluded.fdd < ufl.FirstLoginDate then excluded.fda else ufl.FirstLoginAmt END, LastLoginAmt = case when excluded.ldd > ufl.LastLoginDate then excluded.lda else ufl.LastLoginAmt END, FirstLoginDate = case when excluded.fdd < ufl.FirstLoginDate then excluded.fdd else ufl.FirstLoginDate END, LastLoginDate = case when excluded.ldd > ufl.LastLoginDate then excluded.ldd else ufl.LastLoginDate END; ERROR: column excluded.fdd does not exist LINE 6: SET FirstLoginAmt = case when excluded.fdd < ufl.FirstLogi... ^ ********** Error ********** ERROR: column excluded.fdd does not exist SQL state: 42703 Character: 222
On 2015-12-09 09:01:53 +0000, tarunt@chisquare.in wrote: > drop table daily_deposits; > create table daily_deposits > ( id int, > fdd timestamp, > ldd timestamp, > fda double precision, > lda double precision > ); > > drop table user_first_last; > create table user_first_last > ( UserId int, > FirstLoginDate timestamp, > LastLoginDate timestamp, > FirstLoginAmt double precision, > LastLoginAmt double precision > ); > > > INSERT INTO user_first_last AS ufl (UserId, > FirstLoginDate,LastLoginDate,FirstLoginAmt,LastLoginAmt) > select id, fdd, ldd,fda, lda > from daily_deposits > ON CONFLICT (UserId) > DO UPDATE > SET FirstLoginAmt = case when excluded.fdd < ufl.FirstLoginDate then > excluded.fda else ufl.FirstLoginAmt END, > LastLoginAmt = case when excluded.ldd > ufl.LastLoginDate then > excluded.lda else ufl.LastLoginAmt END, > FirstLoginDate = case when excluded.fdd < ufl.FirstLoginDate then > excluded.fdd else ufl.FirstLoginDate END, > LastLoginDate = case when excluded.ldd > ufl.LastLoginDate then > excluded.ldd else ufl.LastLoginDate END; > > ERROR: column excluded.fdd does not exist > LINE 6: SET FirstLoginAmt = case when excluded.fdd < ufl.FirstLogi... > ^ > ********** Error ********** > > ERROR: column excluded.fdd does not exist > SQL state: 42703 > Character: 222 'excluded' is about the relation you're inserting to (i.e. user_first_last / ufl, which doesn't have a fdd column), not about the table you're selecting from. Regards, Andres
tarunt@chisquare.in schrieb am 09.12.2015 um 10:01: > The following bug has been logged on the website: > > INSERT INTO user_first_last AS ufl (UserId, > FirstLoginDate,LastLoginDate,FirstLoginAmt,LastLoginAmt) > select id, fdd, ldd,fda, lda > from daily_deposits > ON CONFLICT (UserId) > DO UPDATE > SET FirstLoginAmt = case when excluded.fdd < ufl.FirstLoginDate then > excluded.fda else ufl.FirstLoginAmt END, > LastLoginAmt = case when excluded.ldd > ufl.LastLoginDate then > excluded.lda else ufl.LastLoginAmt END, > FirstLoginDate = case when excluded.fdd < ufl.FirstLoginDate then > excluded.fdd else ufl.FirstLoginDate END, > LastLoginDate = case when excluded.ldd > ufl.LastLoginDate then > excluded.ldd else ufl.LastLoginDate END; > > ERROR: column excluded.fdd does not exist > LINE 6: SET FirstLoginAmt = case when excluded.fdd < ufl.FirstLogi... > ^ You already have a perfectly valid answer: http://stackoverflow.com/a/34166088/330315