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.