Merge upsert support?

[nolanbconaway]

Hi! Thanks for this awesome tool.

I'm (finally) writing some unit tests for some internal libraries, in which one of the main functions is completing an upsert using the merge feature.

sql= """
    merge into test as dst
    using TMP_TEST_1698864265 as src
        on dst.a = src.a
    when not matched then
        insert (a,b)
            values (src.a,src.b)
    when matched then
        update set a = src.a, b = src.b
"""

cur.execute(sql)

And i get an error like:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/home/.../3.9.10/envs/aqlib/lib/python3.9/site-packages/fakesnow/fakes.py", line 100, in execute
    return self._execute(command, params, *args, **kwargs)
  File "/home/.../3.9.10/envs/aqlib/lib/python3.9/site-packages/fakesnow/fakes.py", line 164, in _execute
    self._duck_conn.execute(sql, params)
duckdb.ParserException: Parser Error: syntax error at or near "MERGE"
LINE 1: MERGE INTO TEST AS DST USING TMP_TEST_1...

duckdb does not have a MERGE statement (it uses insert-on-conflict), which i presume is the source of this issue. Have you run into this/do you know how you might approach a solution?

For now I am writing out the same merge using INSERT ON CONFLICT depending on the test env, but would be charmed for this to "just work". :)

Info:

• python 3.9
• fakesnow==0.7.0
• duckdb==0.8.1

[tekumara]

I think we could transform simple MERGE statements to INSERT ON CONFLICT, ie: when the clause is when not matched or when matched like in your example.

But if there is a case predicate, eg: when not matched and a < 100 I can't see how to express it in terms of INSERT ON CONFLICT. Likewise for the DELETE action.

We'd probably have to transform this into multiple individual INSERT, UPDATE and DELETE statements.

[sbouclier]

I have the same issue, would be great to support MERGE statements.

[tekumara]

@sbouclier do you have an example of the MERGE statements you'd like support for?

[ngullotta]

Thank you so much for the awesome pytest plugin, this is a great help to testing without wasting a ton of resources.

I would also like to throw my hat in the ring for merge support. It's not that I can't make an upsert do the same as the below, but the aim of my test is to test the functionality of a statement that is pulled in from a different file. So altering the statement at runtime would be tricky

Example of a merge statement I use (excuse the poor formatting):

MERGE INTO 
LINE tgt 
USING (
    SELECT BATCH_NUMBER, ID, ACTIVE_STATUS FROM HEADER WHERE ACTIVE_STATUS = 1
) src
ON tgt.BATCH_NUMBER = src.BATCH_NUMBER
AND tgt.ID = src.ID
AND src.ACTIVE_STATUS IS NULL
WHEN MATCHED THEN UPDATE
   SET tgt.ACTIVE_STATUS = src.ACTIVE_STATUS, tgt.END_DATE = NULL

[nolanbconaway]

Heck yes! This was significant work and I really appreciate what you're doing here @tekumara

[tekumara]

Appreciate the support @nolanbconaway! Thanks must go to @jsibbison-square for pushing forward MERGE support. Do let us know if you find any edge cases that aren't supported.