Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Incorrect SQL query generated for PostgreSQL.upsert #300

Open
gromakovsky opened this issue Mar 5, 2022 · 4 comments · May be fixed by #301
Open

Incorrect SQL query generated for PostgreSQL.upsert #300

gromakovsky opened this issue Mar 5, 2022 · 4 comments · May be fixed by #301

Comments

@gromakovsky
Copy link

I tried using upsert function trying to produce INSERT INTO ... VALUES ... ON CONFLICT DO NOTHING. I passed an empty list of updates and got:

SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near "WHERE"", sqlErrorDetail = "", sqlErrorHint = ""}

I looked at logs and noticed that the following SQL statement is generated:

INSERT INTO “foo_table”(“foo_id”,“foo_bar_id”,“foo_type”) VALUES ... ON CONFLICT (“foo_id”,“foo_bar_id”) DO UPDATE SET WHERE “foo_table”.“foo_id” =... RETURNING ...`

As far as I understand, it is invalid because there is nothing between SET and WHERE. I think it would be more correct to produce DO NOTHING instead of UPDATE SET WHERE... in this case. Note that insertSelectWithConflictCount seems to handle this case correctly.
@parsonsmatt
Copy link
Collaborator

Hm. This is really a persistent-postgresql problem, since we call upsertSql on the SqlBackend given by persistent-postgresql, which does not check for an updateSql of "".

I think I can work up a patch for this. Going to check for an empty list of updates, and if so, we'll do a tet replace of DO UPDATE SET WHERE to DO NOTHING WHERE.

@parsonsmatt
Copy link
Collaborator

Oh, that won't work. We can't do a WHERE if it's a DO NOTHING lol. Even easier.

@parsonsmatt parsonsmatt linked a pull request Mar 5, 2022 that will close this issue
Open
6 tasks
@gromakovsky
Copy link
Author

@parsonsmatt oh, thanks for such a quick fix 😊
I have tested it in my code and can confirm that it works as expected now (at least in my case) 👍

@parsonsmatt
Copy link
Collaborator

The code in my PR will break if nothing is inserted. I'd recommend using something else

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Support upsert with empty updates bitemyapp/esqueleto
2 participants
@gromakovsky @parsonsmatt