Use excluded(column) in filter_target

[deved99]

Hello,

I have a table like this:

CREATE TABLE posts (
  title TEXT PRIMARY KEY,
  created TIMESTAMP WITH TIMEZONE NOT NULL,
  modified TIMESTAMP WITH TIMEZONE NOT NULL
)

I have this struct:

#[derive(Debug, Queryable, Insertable)]
struct Post {
  title: String,
  created: DateTime<Utc>,
  modified: DateTime<Utc>
}

I would like to upsert many posts at once, updating a row only if the modified column I'm inserting is greater then the one already in the table. I've tried this:

        diesel::insert_into(posts)
            .values(new_posts)
            .on_conflict(title)
            .filter_target(modified.lt(excluded(modified)))
            .do_update()
            .set((
                created.eq(excluded(created)),
                modified.eq(excluded(modified)),
            ))
            .execute(&mut conn)

But this returns the following error:

Diesel(DatabaseError(Unknown, "invalid reference to FROM-clause entry for table \"excluded\""))

Any suggestions?

[weiznich]

There are multiple points to address in this question:

• The invalid generated SQL is another case of excluded is allowed in places it shouldn't be #799. That's a bug in diesel and that should not happen. Mind adding that reproduction to the linked issue?
• The query you construct is not doing what you would expect. It applies the filter to the ON CONFLICT target, but you want to apply the filter to the update part. That's Upsert with where clause Postgres implementation is wrong #3330, which is currently only implemented on diesels master branch.

[deved99]

Hi! Sorry for the (long) delay. Would the reproduction at point 1 still be useful?

[weiznich]

Yes that would still be helpful.

[deved99]

Sorry for the very long delay, I just added it.

Thank you!