Repository: tobymao/sqlglot

Issue:
DO NOTHING Breaks Merge Parsing for Postgres
[Postgres supports `DO NOTHING`](https://www.postgresql.org/docs/current/sql-merge.html) on matching/not matching a given join condition for MERGE. However, sqlglot isn't able to parse such statements:

```python
parsed = sqlglot.parse_one("""
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
WHEN MATCHED AND target.name IS NOT NULL THEN
    DO NOTHING;
"""
, dialect="postgres")
```

Leads to:

```
ParseError: Required keyword: 'then' missing for <class 'sqlglot.expressions.When'>. Line 12, Col: 3.
  .name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
WHEN MATCHED AND target.name IS NOT NULL THEN
    DO NOTHING;
```

Discussion:
@georgesittas: Fixed by #4087

Solution patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 6bbd0fb4fa..21d3a8fd28 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -6279,6 +6279,7 @@ class Merge(Expression):
         "on": True,
         "expressions": True,
         "with": False,
+        "returning": False,
     }
 
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 9a346b24d1..6e11d68c41 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3587,9 +3587,12 @@ def merge_sql(self, expression: exp.Merge) -> str:
         on = f"ON {self.sql(expression, 'on')}"
         expressions = self.expressions(expression, sep=" ", indent=False)
         sep = self.sep()
+        returning = self.expressions(expression, key="returning", indent=False)
+        returning = f"RETURNING {returning}" if returning else ""
 
         return self.prepend_ctes(
-            expression, f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{expressions}"
+            expression,
+            f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{expressions}{sep}{returning}",
         )
 
     def tochar_sql(self, expression: exp.ToChar) -> str:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a15dd8155e..840f2f13ee 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -6825,6 +6825,7 @@ def _parse_merge(self) -> exp.Merge:
             using=using,
             on=on,
             expressions=self._parse_when_matched(),
+            returning=self._match(TokenType.RETURNING) and self._parse_csv(self._parse_bitwise),
         )
 
     def _parse_when_matched(self) -> t.List[exp.When]:
@@ -6865,7 +6866,7 @@ def _parse_when_matched(self) -> t.List[exp.When]:
             elif self._match(TokenType.DELETE):
                 then = self.expression(exp.Var, this=self._prev.text)
             else:
-                then = None
+                then = self._parse_var_from_options(self.CONFLICT_ACTIONS)
 
             whens.append(
                 self.expression(


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index f3f21a9b56..1b89ecd1d5 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -794,6 +794,9 @@ def test_postgres(self):
         self.validate_identity(
             'SELECT js, js IS JSON ARRAY WITH UNIQUE KEYS AS "array w. UK?", js IS JSON ARRAY WITHOUT UNIQUE KEYS AS "array w/o UK?", js IS JSON ARRAY UNIQUE KEYS AS "array w UK 2?" FROM t'
         )
+        self.validate_identity(
+            "MERGE INTO target_table USING source_table AS source ON target.id = source.id WHEN MATCHED THEN DO NOTHING WHEN NOT MATCHED THEN DO NOTHING RETURNING MERGE_ACTION(), *"
+        )
 
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier