goodybag
Conflict target's where should not have a table name
It is nicely stated in the conflict helper that where doesn't need a table name, but it still gets it. https://github.com/goodybag/mongo-sql/blob/1351b48b3dc0b9db30b3cb0f223ee2f7713fb376/helpers/query/conflict.js#L69
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";
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
-- ]
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.
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