fix(postgres): update upsert regex to match the last RETURNING *

[Americas]

Pull Request check-list

Please make sure to review and check all of these items:

• Does npm run test or npm run test-DIALECT pass with this change (including linting)?
• Does the description below contain a link to an existing issue (Closes #[issue]) or a description of the issue you are solving?
• Have you added new tests to prevent regressions?
• Is a documentation update included (if this change modifies existing APIs, or introduces new ones)?
• Did you update the typescript typings accordingly (if applicable)?
• Did you follow the commit message conventions explained in CONTRIBUTING.md?

Description of change

Fixes #11523

Postgres' upsert query generator replaces the first RETURNING * it finds on the insert and update query, so if the data being added has that string, it replaces it (so it replaces the wrong RETURNING *) changing the input data and messing with the the final query.

This PR updates the replace method to use a regex with a negative lookahead so it only chooses the last RETURNING * in the query.

[codecov]

Codecov Report

Merging #11538 into master will increase coverage by <.01%.
The diff coverage is 100%.

@@            Coverage Diff             @@
##           master   #11538      +/-   ##
==========================================
+ Coverage   96.27%   96.27%   +<.01%     
==========================================
  Files          94       94              
  Lines        9185     9187       +2     
==========================================
+ Hits         8843     8845       +2     
  Misses        342      342

Impacted Files	Coverage Δ
lib/dialects/postgres/query-generator.js	94.38% <100%> (+0.02%)	⬆️

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update bd59b87...1655b1f. Read the comment docs.

[papb]

Nice! LGTM

[papb]

I have edited

Fixes issue #11523.

to the following

Fixes #11523

so that GitHub detects the fix automatically:

[papb]

By the way, I have a question, was the SSCCE (papb/sequelize-sscce#9) useful to you? Or do you think you could have fixed the issue as easily without it?

[Americas]

By the way, I have a question, was the SSCCE (papb/sequelize-sscce#9) useful to you? Or do you think you could have fixed the issue as easily without it?

I used it yes, just changed the log to an error if the result was different from the expected. It helps to have the problem easily available, otherwise there is this great setup cost to start analyzing the issue 😄

[papb]

@Americas Nice! I'm happy that my idea of an SSCCE repo was useful then :)

[papb]

I was thinking... Would you be willing to add an explicit test for the JSONB case as well?

[SimonSchick]

Looks good.

[SimonSchick]

Nit: Regex should be moved to a constant.

[Americas]

Done.

[Americas]

I was thinking... Would you be willing to add an explicit test for the JSONB case as well?

Added JSON example from SSCCE and a normal example.

[sushantdhiman]

🎉 This PR is included in version 5.21.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀