Support `WHERE()` on `INSERT ON CONFLICT DO UPDATE`

[yz89122]

Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

As title.

Docs: https://www.postgresql.org/docs/current/sql-insert.html

Postgres support WHERE on DO UPDATE. I.g.

  table.Resource.
    INSERT(
      table.Resource.ID,
      table.Resource.ParentID,
      table.Resource.Name,
    ).
    VALUES(
      resource.ID,
      resource.ParentID,
      resource.Name,
    ).
    ON_CONFLICT(table.Resource.ID).
    DO_UPDATE(postgres.SET(
      table.Resource.Name.SET(table.Resource.EXCLUDED.Name),
    )).
    WHERE(
      table.Resource.ParentID.EQ(postgres.UUID(parent.ID)).
    ).
    RETURNING(
      table.Resource.ID,
      table.Resource.ParentID,
      table.Resource.Name,
    )

With this expression, I can do upsert with 1 query and validate whether the resource is belong to the parent. Without this expression, I need a extra SELECT statement to achieve this, which increase the transaction duration.

---

Describe the solution you'd like A clear and concise description of what you want to happen.

  table.Resource.
    INSERT(
      table.Resource.ID,
      table.Resource.ParentID,
      table.Resource.Name,
    ).
    VALUES(
      resource.ID,
      resource.ParentID,
      resource.Name,
    ).
    ON_CONFLICT(table.Resource.ID).
    DO_UPDATE(postgres.SET(
      table.Resource.Name.SET(table.Resource.EXCLUDED.Name),
    )).
+   WHERE(
+     table.Resource.ParentID.EQ(postgres.UUID(parent.ID)).
+   ).
    RETURNING(
      table.Resource.ID,
      table.Resource.ParentID,
      table.Resource.Name,
    )

[houten11]

Jet already supports WHERE on INSERT, either for conflict target or conflict action:

    // on conflict target
    ON_CONFLICT(table.Resource.ID).WHERE(table.Resource.ParentID.EQ(postgres.UUID(parent.ID)))
    DO_UPDATE(postgres.SET(
      table.Resource.Name.SET(table.Resource.EXCLUDED.Name),
    )).

    // on conflict action
    ON_CONFLICT(table.Resource.ID)
    DO_UPDATE(postgres.SET(
             table.Resource.Name.SET(table.Resource.EXCLUDED.Name),
        ).WHERE(
             table.Resource.ParentID.EQ(postgres.UUID(parent.ID)).
        ),
    ).

[yz89122]

Hi @houten11, thanks!