Repository: tobymao/sqlglot

Issue:
Missing SAFE_DIVIDE translation for postgres
`SAFE_DIVIDE` from BigQuery is not translated when writing to Postgres, resulting in invalid SQL. For example: 

```python
sqlglot.transpile('SELECT SAFE_DIVIDE(1,0) AS hm', read="bigquery", write="postgres")[0]
# Output: 'SELECT SAFE_DIVIDE(1, 0) AS hm'
```

It should transpile to a `CASE`, or `NULLIF` because that function doesn't exist in postgres. I'm completely new to this codebase, but it looks like the fix this is to add `exp.SafeDivide: no_safe_divide_case_sql` to https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/postgres.py#L579 with the appropriate import.

And define:
```python
def no_safe_divide_case_sql(self: Generator, expression: exp.SafeDivide) -> str:
    n = self.sql(expression, "this")
    d = self.sql(expression, "expression")
    return f"CASE WHEN ({d}) <> 0 THEN ({n}) / ({d}) ELSE NULL END"
```
in https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/dialect.py

Docs:
- https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#safe_divide

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 390e1aacf0..922539e0fa 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -957,6 +957,7 @@ class Generator(generator.Generator):
             exp.Uuid: lambda *_: "GENERATE_UUID()",
             exp.Values: _derived_table_values_to_unnest,
             exp.VariancePop: rename_func("VAR_POP"),
+            exp.SafeDivide: rename_func("SAFE_DIVIDE"),
         }
 
         SUPPORTED_JSON_PATH_PARTS = {
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 532ab854c5..2c23d71921 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -1037,12 +1037,6 @@ def no_recursive_cte_sql(self: Generator, expression: exp.With) -> str:
     return self.with_sql(expression)
 
 
-def no_safe_divide_sql(self: Generator, expression: exp.SafeDivide, if_sql: str = "IF") -> str:
-    n = self.sql(expression, "this")
-    d = self.sql(expression, "expression")
-    return f"{if_sql}(({d}) <> 0, ({n}) / ({d}), NULL)"
-
-
 def no_tablesample_sql(self: Generator, expression: exp.TableSample) -> str:
     self.unsupported("TABLESAMPLE unsupported")
     return self.sql(expression.this)
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 139f803a0a..9c9b25f4e2 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -20,7 +20,6 @@
     build_formatted_time,
     inline_array_unless_query,
     no_comment_column_constraint_sql,
-    no_safe_divide_sql,
     no_time_sql,
     no_timestamp_sql,
     pivot_column_names,
@@ -615,7 +614,6 @@ class Generator(generator.Generator):
             exp.Return: lambda self, e: self.sql(e, "this"),
             exp.ReturnsProperty: lambda self, e: "TABLE" if isinstance(e.this, exp.Schema) else "",
             exp.Rand: rename_func("RANDOM"),
-            exp.SafeDivide: no_safe_divide_sql,
             exp.SHA: rename_func("SHA1"),
             exp.SHA2: sha256_sql,
             exp.Split: rename_func("STR_SPLIT"),
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index acaa609956..f1883a95d5 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -20,7 +20,6 @@
     min_or_least,
     no_ilike_sql,
     no_recursive_cte_sql,
-    no_safe_divide_sql,
     no_trycast_sql,
     regexp_extract_sql,
     regexp_replace_sql,
@@ -550,7 +549,6 @@ class Generator(generator.Generator):
             exp.RegexpLike: lambda self, e: self.binary(e, "RLIKE"),
             exp.RegexpSplit: rename_func("SPLIT"),
             exp.Right: right_to_substring_sql,
-            exp.SafeDivide: no_safe_divide_sql,
             exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
             exp.ArrayUniqueAgg: rename_func("COLLECT_SET"),
             exp.Split: lambda self, e: self.func(
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 8f44027b40..0c2079d8e4 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -611,6 +611,7 @@ class Generator(generator.Generator):
             exp.UnixToTime: _unix_to_time_sql,
             exp.Levenshtein: _levenshtein_sql,
         }
+
         TRANSFORMS.pop(exp.CommentColumnConstraint)
 
         PROPERTIES_LOCATION = {
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index a87a5fce00..539ca95bb6 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -16,7 +16,6 @@
     left_to_substring_sql,
     no_ilike_sql,
     no_pivot_sql,
-    no_safe_divide_sql,
     no_timestamp_sql,
     regexp_extract_sql,
     rename_func,
@@ -418,7 +417,6 @@ class Generator(generator.Generator):
             exp.RegexpExtract: regexp_extract_sql,
             exp.RegexpExtractAll: regexp_extract_sql,
             exp.Right: right_to_substring_sql,
-            exp.SafeDivide: no_safe_divide_sql,
             exp.Schema: _schema_sql,
             exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
             exp.Select: transforms.preprocess(
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 0601a36781..b62e70ae57 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -22,7 +22,6 @@
     timestrtotime_sql,
     var_map_sql,
     map_date_part,
-    no_safe_divide_sql,
     no_timestamp_sql,
     timestampdiff_sql,
     no_make_interval_sql,
@@ -955,7 +954,6 @@ class Generator(generator.Generator):
                     _transform_generate_date_array,
                 ]
             ),
-            exp.SafeDivide: lambda self, e: no_safe_divide_sql(self, e, "IFF"),
             exp.SHA: rename_func("SHA1"),
             exp.StarMap: rename_func("OBJECT_CONSTRUCT"),
             exp.StartsWith: rename_func("STARTSWITH"),
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index d7d52b551d..37842cc055 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3410,6 +3410,11 @@ def div_sql(self, expression: exp.Div) -> str:
 
         return self.binary(expression, "/")
 
+    def safedivide_sql(self, expression: exp.SafeDivide) -> str:
+        n = exp._wrap(expression.this, exp.Binary)
+        d = exp._wrap(expression.expression, exp.Binary)
+        return self.sql(exp.If(this=d.neq(0), true=n / d, false=exp.Null()))
+
     def overlaps_sql(self, expression: exp.Overlaps) -> str:
         return self.binary(expression, "OVERLAPS")
 


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index dbe4401af6..0ce2a52133 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1572,14 +1572,30 @@ def test_bigquery(self):
             "SAFE_DIVIDE(x, y)",
             write={
                 "bigquery": "SAFE_DIVIDE(x, y)",
-                "duckdb": "IF((y) <> 0, (x) / (y), NULL)",
-                "presto": "IF((y) <> 0, (x) / (y), NULL)",
-                "trino": "IF((y) <> 0, (x) / (y), NULL)",
-                "hive": "IF((y) <> 0, (x) / (y), NULL)",
-                "spark2": "IF((y) <> 0, (x) / (y), NULL)",
-                "spark": "IF((y) <> 0, (x) / (y), NULL)",
-                "databricks": "IF((y) <> 0, (x) / (y), NULL)",
-                "snowflake": "IFF((y) <> 0, (x) / (y), NULL)",
+                "duckdb": "CASE WHEN y <> 0 THEN x / y ELSE NULL END",
+                "presto": "IF(y <> 0, CAST(x AS DOUBLE) / y, NULL)",
+                "trino": "IF(y <> 0, CAST(x AS DOUBLE) / y, NULL)",
+                "hive": "IF(y <> 0, x / y, NULL)",
+                "spark2": "IF(y <> 0, x / y, NULL)",
+                "spark": "IF(y <> 0, x / y, NULL)",
+                "databricks": "IF(y <> 0, x / y, NULL)",
+                "snowflake": "IFF(y <> 0, x / y, NULL)",
+                "postgres": "CASE WHEN y <> 0 THEN CAST(x AS DOUBLE PRECISION) / y ELSE NULL END",
+            },
+        )
+        self.validate_all(
+            "SAFE_DIVIDE(x + 1, 2 * y)",
+            write={
+                "bigquery": "SAFE_DIVIDE(x + 1, 2 * y)",
+                "duckdb": "CASE WHEN (2 * y) <> 0 THEN (x + 1) / (2 * y) ELSE NULL END",
+                "presto": "IF((2 * y) <> 0, CAST((x + 1) AS DOUBLE) / (2 * y), NULL)",
+                "trino": "IF((2 * y) <> 0, CAST((x + 1) AS DOUBLE) / (2 * y), NULL)",
+                "hive": "IF((2 * y) <> 0, (x + 1) / (2 * y), NULL)",
+                "spark2": "IF((2 * y) <> 0, (x + 1) / (2 * y), NULL)",
+                "spark": "IF((2 * y) <> 0, (x + 1) / (2 * y), NULL)",
+                "databricks": "IF((2 * y) <> 0, (x + 1) / (2 * y), NULL)",
+                "snowflake": "IFF((2 * y) <> 0, (x + 1) / (2 * y), NULL)",
+                "postgres": "CASE WHEN (2 * y) <> 0 THEN CAST((x + 1) AS DOUBLE PRECISION) / (2 * y) ELSE NULL END",
             },
         )
         self.validate_all(