Upsert in Postgres 9.5

Question

Trying to do an upsert to maintain user first last login timestamp and platform. But the newly introduced upsert (insert on conflict) does not allow for inserting the output of a select query. Tried using With but to no use. Does the new insert support inserting output of a query and update existing fields in case of primary key conflict. Below is the query used:

INSERT INTO user_first_last (UserId, FirstLoginDate, LastLoginDate,FirstLoginAmt,LastLoginAmt)
select id, fdd,  ldd,  fda,  lda from daily_activity as dp
ON CONFLICT (UserId)
DO UPDATE 
SET         FirstLoginAmt = case when dp.fdd < FirstLoginDate then dp.fda else FirstLoginAmt END,
            LastLoginAmt = case when dp.ldd > LastLoginDate then dp.lda else LastLoginAmt END,
        FirstLoginDate = case when dp.fdd < FirstLoginDate then dp.fdd else FirstLoginDate END,
            LastLoginDate = case when dp.ldd > LastLoginDate then dp.ldd else LastLoginDate END;

It throws error:

missing FROM-clause entry for table "dp"
ERROR:  missing FROM-clause entry for table "dp"
LINE 8: SET         FirstLoginAmt = case when dp.fdd < FirstLoginDate ...

Using excluded gives error again:

INSERT INTO user_first_last (UserId, FirstLoginDate,LastLoginDate,FirstLoginAmt,LastLoginAmt) 
select id, fdd,  ldd,fda,  lda 
from daily_activity 
ON CONFLICT (UserId) 
DO UPDATE  
  SET FirstLoginAmt = case when excluded.fdd < FirstLoginDate then excluded.fda else FirstLoginAmt END, 
      LastLoginAmt = case when excluded.ldd > LastLoginDate then excluded.lda else LastLoginAmt END,FirstLoginDate = case when excluded.fdd < FirstLoginDate then excluded.fdd else FirstLoginDate END,
      LastLoginDate = case when excluded.ldd > LastLoginDate then excluded.ldd else LastLoginDate END;

Error: ERROR: column excluded.fdd does not exist

I tried the following, please use to replicate at your end, this is still giving error:

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

Answer 1

Per the documentation:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

The special record exluded is of the same type as the table user_first_last.

insert into user_first_last as u
    (userid, firstlogindate,lastlogindate,firstloginamt,lastloginamt) 
    select id, fdd, ldd, fda, lda 
    from daily_activity 
on conflict (userid) 
    do update set
    firstloginamt = case 
        when excluded.firstlogindate < u.firstlogindate 
        then excluded.firstloginamt 
        else u.firstloginamt 
    end, 
    lastloginamt = case 
        when excluded.lastlogindate > u.lastlogindate 
        then excluded.lastloginamt 
        else u.lastloginamt 
    end,
    firstlogindate = case 
        when excluded.firstlogindate < u.firstlogindate 
        then excluded.firstlogindate 
        else u.firstlogindate 
    end,
    lastlogindate = case 
        when excluded.lastlogindate > u.lastlogindate 
        then excluded.lastlogindate 
        else u.lastlogindate 
    end;

Answer 2

I ran into this issue as well, and wanted to add a clarifying message to other, correct answer.

'excluded' refers to the row that you're trying to insert which was rejected.

The difference is, in this example, that you need to reference excluded.firstlogindate instead of excluded.fdd because firstlogindate is the name of the column upon insertion.