Documentation: Improve documentation for upsert `.filter_target()`

[liningpan]

Setup

Versions

• Rust: 1.70
• Diesel: 2.1.0
• Database: Postgres (cockroach)
• Operating System: Mac OS 13

Feature Flags

• diesel: ["chrono", "uuid", "serde_json"]

Problem Description

Unless I missed something, I was not able to find an example for using .filter_target() in the inline documentation. This corresponds to ON CONFLICT ... WHERE index_predicate DO UPDATE SET ..., which is different from the WHERE clause after DO UPDATE. It took me a bit of digging to figure out that diesel actually supports this.

It would be great if the we can add an example for this use case.

What are you trying to accomplish?

Using the ON CONFLICT ... WHERE index_predicate syntax with partial unique indexes.

What is the expected output?

N/A

What is the actual output?

N/A

Are you seeing any additional errors?

N/A

Steps to reproduce

Documentation for normal on conflict do update with extensive examples. https://docs.diesel.rs/2.1.x/diesel/upsert/struct.IncompleteOnConflict.html#examples

filter_target function is grouped with the trait https://docs.diesel.rs/2.1.x/diesel/upsert/struct.IncompleteOnConflict.html#method.filter_target

Checklist

• [x] I have already looked over the issue tracker and the discussion forum for similar possible closed issues.
• [x] (N/A, inline doc) This issue can be reproduced on Rust's stable channel. (Your issue will be closed if this is not the case)
• [x] (N/A, inline doc) This issue can be reproduced without requiring a third party crate

Potential solution

I propose that all the examples can be moved to the upsert module or the IncompleteOnConflict struct, which will make more sense if we were to add an example using filter_target()

PS: There should be a separate issue template documentation related issues.

[weiznich]

Thanks for writing this bug report. Please complete the issue template. Its mandatory for issues in our tracker.. If you don't feel that your report fits into the template consider moving it to discussion forum.

[weiznich]

Thanks for updating the issue. Strictly speaking that's not a bug report, but a feature request.

We are happy to receive contributions for this, so feel free to open a PR that adds the corresponding examples.

[AshleySchaeffer]

@liningpan I've just come across this too. Trying to figure out how to use filter_target to handle a conflict associated with a partial unique index. Any tips?

EDIT: @weiznich I'd be happy to contribute once I figure out how to use it.

UPDATE: I got it working. Again, happy to provide input/contribute. In short, given this table and partial unique index:

CREATE TYPE token_kind AS ENUM ('foo', 'bar', 'baz');

CREATE TABLE tokens (
  id uuid PRIMARY KEY,
  user_id uuid REFERENCES users (id) NOT NULL,
  token TEXT NOT NULL,
  kind token_kind NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Note: `user_id` and the WHERE clause.
CREATE UNIQUE INDEX unique_kind ON tokens (user_id) WHERE kind IN ('foo', 'bar', 'baz');

You can do an upsert like this:

#[derive(Debug, diesel_derive_enum::DbEnum)]
#[ExistingTypePath = "crate::schema::sql_types::TokenKind"]
pub enum TokenKind {
    Foo,
    Bar,
    Baz,
}

/// `&self` derives `Insertable`
pub fn upsert(&self, conn: &mut DBConnection) -> QueryResult<Self> {
    diesel::insert_into(schema::table)
        .values(&*self)
       // Matches the columns defined in `ON <table> (<column>)` 
        .on_conflict(schema::user_id)
       // The filter criteria MUST match the `WHERE` clause of the partial unique index
        .filter_target(schema::kind.eq_any(&[TokenKind::Foo, TokenKind::Bar, TokenKind::Baz]))
        .do_update()
        .set((
            schema::token.eq(self.token()),
            schema::updated_at.eq(Utc::now()),
        ))
        .get_result(conn)
}