How to do INSERT INTO SELECT and ON DUPLICATE UPDATE in PostgreSQL 9.5?

Question

I'm trying to to do the following in PostgreSQL

INSERT INTO blog_sums ( blog_id, date, total_comments)
    SELECT blog_id, '2016-09-22', count(comment_id) as total_comments_update
    FROM blog_comments
    WHERE date = '2016-09-22'
    GROUP BY blog_id         
ON CONFLICT (blog_id ,date)
DO UPDATE SET blog_sums.total_comments = total_comments_update;

I have unique key on date + blog_id and I keep getting Error:

ERROR: column "total_comments_update" does not exist

I'm looking for the "right" way and the most efficient way to do update on duplicate/conflict in this case

my tables are

blog_comments (blog_id, comment_id, comment, date)
blog_sums ( blog_id, date, total_comments) . unique on blog_id+date

Thanks

Answer 1

You cannot access the column aliases from the select in the DO UPDATE SET clause. You can use the excluded table alias which includes all rows that failed to insert because of conflicts:

INSERT INTO blog_sums ( blog_id, date, total_comments)
    SELECT blog_id, '2016-09-22', count(comment_id) as total_comments_update
    FROM blog_comments
    WHERE date = '2016-09-22'
    GROUP BY blog_id         
ON CONFLICT (blog_id ,date)
DO UPDATE SET total_comments = excluded.total_comments;

So excluded.total_comments in the last line refers to the value of total_comments that we tried to insert but we couldn't, because of the conflict.