Upsert в Postgres 9.5

Попытка выполнить обновление, чтобы сохранить отметку времени первого последнего входа пользователя и платформу. Но недавно представленный upsert (вставка при конфликте) не позволяет вставлять вывод запроса на выборку. Пробовал использовать With, но без толку. Поддерживает ли новая вставка вставку вывода запроса и обновление существующих полей в случае конфликта первичных ключей. Ниже используется запрос:

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;

Выдает ошибку:

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

Использование excluded снова дает ошибку:

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;

Ошибка: ОШИБКА: столбец исключен. Fdd не существует

Я пробовал следующее, используйте для репликации на вашей стороне, это все еще дает ошибку:

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

postgresql sql unique postgresql-9.5 upsert
person Tarun    schedule 08.12.2015    source источник
comment
извините, stackoverflow.com/users/330315/a-horse-with-no-name , добавили код.   -  person Tarun    schedule 08.12.2015
comment
Отредактировал вопрос, чтобы включить правильное форматирование кода.   -  person Mikko Ohtamaa    schedule 08.12.2015
comment
спасибо @MikkoOhtamaa за форматирование   -  person Tarun    schedule 08.12.2015
comment
Доступ к строкам, которые не были вставлены, доступен через псевдоним excluded: postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT   -  person a_horse_with_no_name    schedule 08.12.2015
comment
использование excluded дает ошибку: ОШИБКА: столбец excluded.fdd не существует   -  person Tarun    schedule 08.12.2015
comment
Вам необходимо получить к ним доступ с помощью имен столбцов таблицы target. Что-то вроде when excluded.FirstLoginDate < FirstLoginDate или, может быть, when excluded.FirstLoginDate < user_first_last.FirstLoginDate   -  person a_horse_with_no_name    schedule 08.12.2015
comment
Пожалуйста, приведите пример, я полностью потерялся здесь и относительно новичок в postgres   -  person Tarun    schedule 08.12.2015
comment
@a_horse_with_no_name Пытался, как вы предложили, по-прежнему дает ту же ошибку, мне удалось это сделать с помощью mysql upsert, но не удалось воспроизвести то же самое в Postgres 9.5.   -  person Tarun    schedule 09.12.2015
comment
PostgreSQL 9.5beta2, скомпилированный Visual C ++ build 1800, 64-разрядная версия на Windows 7 professional   -  person Tarun    schedule 09.12.2015

Ответы (2)


arrow_upward
5
arrow_downward

Согласно документации:

Предложения SET и WHERE в ON CONFLICT DO UPDATE имеют доступ к существующей строке с использованием имени таблицы (или псевдонима) и к строкам, предлагаемым для вставки с использованием специальной таблицы excluded.

Специальная запись exluded того же типа, что и таблица 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;
person klin    schedule 08.12.2015

arrow_upward
3
arrow_downward

Я тоже столкнулся с этой проблемой и хотел добавить уточняющее сообщение к другому правильному ответу.

«исключено» относится к строке, которую вы пытаетесь вставить, но которая была отклонена.

Разница в том, что в этом примере вам нужно ссылаться на excluded.firstlogindate вместо excluded.fdd, потому что firstlogindate - это имя столбца при вставке.

person Josh    schedule 26.07.2017