Conflict target's where should not have a table name

[ivankustera]

It is nicely stated in the conflict helper that where doesn't need a table name, but it still gets it.

mongo-sql/helpers/query/conflict.js

Line 69 in 1351b48

// Where condition doesn't need a table name

If I try something like

{
  type: 'insert',
  table: 'entries',
  values: [
    {
      orgId: '...',
      otherId: '...',
      amount: 1200,
    },
  ],
  conflict: {
    target: {
      columns: [
        'orgId',
        'otherId',
      ],
      where: {
        isDeleted: false,
      },
    },
    action: {
      update: {
        amount: '$excluded.amount$',
      },
    },
  },
}

The result is

insert into "entries" ("orgId", "otherId", "amount") values ($1, $2, $3) 
on conflict ("orgId", "otherId") where "entries"."isDeleted" is false do update set "amount" = "excluded"."amount";

[jrf0110]

Hrmmm looking at the Postgres docs, it looks like the where clause for the conflict is for an index_predicate, not a proper conditional block. So, that would be a bug in MoSQL.

However, I think you wanted your where clause to be for the update, right? If you move your where to the action block, I think you'll get a query that makes more sense.

{
  type: 'insert',
  table: 'entries',
  values: [
    {
      orgId: '...',
      otherId: '...',
      amount: 1200,
    },
  ],
  conflict: {
    target: {
      columns: [
        'orgId',
        'otherId',
      ],
    },
    action: {
      update: {
        amount: '$excluded.amount$',
      },
      where: {
        isDeleted: false,
      },
    },
  },
}

Result

insert into "entries" ("orgId",
                       "otherId",
                       "amount")
values ($1, $2, $3) on conflict ("orgId", "otherId")
where "entries"."isDeleted" is false do
  update
  set "amount" = "excluded"."amount" where "entries"."isDeleted" is false
-- Values:
-- [
--   "...",
--   "...",
--   1200
-- ]

[ivankustera]

Well, I forgot to mention that I want to handle conflicts on the partial unique index. It is defined with
create unique index my_index on entries ("orgId", "otherId") where "isDeleted" is false.

In that case conflict target cannot be just a list of columns since that doesn't match the index. If I write manually query as

values ($1, $2, $3) 
on conflict ("orgId", "otherId") where "isDeleted" is false 
do update set "amount" = "excluded"."amount";

it works perfectly.
But unfortunately mongo-sql translates it with a table name and then Postgres treats it as a condition which cannot be placed there.

[jrf0110]

Ahhhh I see.

You can always fallback to using the $custom helper in scenarios like this

{
  type: 'insert',
  table: 'entries',
  values: [
    {
      orgId: '...',
      otherId: '...',
      amount: 1200,
    },
  ],
  conflict: {
    target: {
      columns: [
        'orgId',
        'otherId',
      ],
      where: {
        $custom: ['"isDeleted" is false']
      }
    },
    action: {
      update: {
        amount: '$excluded.amount$',
      },
    },
  },
}

Result

insert into "entries" ("orgId",
                       "otherId",
                       "amount")
values ($1, $2, $3) on conflict ("orgId", "otherId")
where "isDeleted" is false do
  update
  set "amount" = "excluded"."amount"
-- Values:
-- [
--   "...",
--   "...",
--   1200
-- ]

Not super ideal I know

[ivankustera]

Yeah, I forgot about it. $custom worked just fine.
Thanks