Repository: tobymao/sqlglot
Pull request: feat(postgres)!: Add support for ANY_VALUE for versions 16+
Fixes https://github.com/TobikoData/sqlmesh/issues/4674

Postgres 16 (released 24 Sep 2023) added support for `ANY_VALUE`. As this may be too recent for some Postgres workloads, I thought it'd make for another use case of `Version`, but if we want to simplify we can instead always preserve the roundtrip.


Docs
---------
[PSQL ANY_VALUE](https://www.postgresql.org/docs/16/functions-aggregate.html) | [PSQL Feature Matrix](https://www.postgresql.org/about/featurematrix/)

Problem statement:
Support any_value aggregate function for PostgreSQL dialect
Description:

Currently, the `any_value()` aggregate function is not supported in the Postgres dialect. This function is useful when any non-deterministic non-null value from a group is acceptable. It is currently being replaced by `max()`, which cannot be used with some data types (e.g., UUID).

Model to reproduce:

```sql
MODEL (
    name test.my_model
);

SELECT
    ANY_VALUE(1) AS col
```

After formatting it becomes this:

```sql
MODEL (
    name test.my_model
);

SELECT
    MAX(1) AS col
```

---

sqlmesh v0.189.1

Hints from issue discussion:

Commit message: feat(postgres): Add support for ANY_VALUE

Patch to review:
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 949bea5cc3..8ddfa22946 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -36,6 +36,7 @@
     strposition_sql,
     count_if_to_sum,
     groupconcat_sql,
+    Version,
 )
 from sqlglot.generator import unsupported_args
 from sqlglot.helper import is_int, seq_get
@@ -255,6 +256,15 @@ def _levenshtein_sql(self: Postgres.Generator, expression: exp.Levenshtein) -> s
     return rename_func(name)(self, expression)
 
 
+def _versioned_anyvalue_sql(self: Postgres.Generator, expression: exp.AnyValue) -> str:
+    # https://www.postgresql.org/docs/16/functions-aggregate.html
+    # https://www.postgresql.org/about/featurematrix/
+    if self.dialect.version < Version("16.0"):
+        return any_value_to_max_sql(self, expression)
+
+    return rename_func("ANY_VALUE")(self, expression)
+
+
 class Postgres(Dialect):
     INDEX_OFFSET = 1
     TYPED_DIVISION = True
@@ -546,7 +556,7 @@ class Generator(generator.Generator):
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
-            exp.AnyValue: any_value_to_max_sql,
+            exp.AnyValue: _versioned_anyvalue_sql,
             exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CAT"),
             exp.ArrayFilter: filter_array_using_unnest,
             exp.BitwiseXor: lambda self, e: self.binary(e, "#"),
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 055746c514..b0fee26c8e 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1436,3 +1436,21 @@ def test_json_extract(self):
                         "clickhouse": "SELECT JSONExtractString(foo, '12')",
                     },
                 )
+
+    def test_any_value(self):
+        self.validate_all(
+            "SELECT ANY_VALUE(1) AS col",
+            write={
+                "postgres, version=15": "SELECT MAX(1) AS col",
+                "postgres, version=13.9": "SELECT MAX(1) AS col",
+            },
+        )
+
+        self.validate_all(
+            "SELECT ANY_VALUE(1) AS col",
+            write={
+                "postgres": "SELECT ANY_VALUE(1) AS col",
+                "postgres, version=16": "SELECT ANY_VALUE(1) AS col",
+                "postgres, version=17.5": "SELECT ANY_VALUE(1) AS col",
+            },
+        )
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 1daec00125..619f00dac6 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -778,7 +778,7 @@ def test_presto(self):
                 "hive": "FIRST(x)",
                 "mysql": "ANY_VALUE(x)",
                 "oracle": "ANY_VALUE(x)",
-                "postgres": "MAX(x)",
+                "postgres": "ANY_VALUE(x)",
                 "presto": "ARBITRARY(x)",
                 "redshift": "ANY_VALUE(x)",
                 "snowflake": "ANY_VALUE(x)",


Code review comments:
diff_hunk: @@ -1436,3 +1436,21 @@ def test_json_extract(self):
                         "clickhouse": "SELECT JSONExtractString(foo, '12')",
                     },
                 )
+
+    def test_any_value(self):
+        self.validate_all(
+            "SELECT ANY_VALUE(1) AS col",
+            write={
+                "postgres, version=15": "SELECT MAX(1) AS col",
+                "postgres, version=13.9": "SELECT MAX(1) AS col",
+            },
+        )
+
+        self.validate_all(
+            "SELECT ANY_VALUE(1) AS col",
+            write={
+                "postgres": "SELECT ANY_VALUE(1) AS col",
+                "postgres, version=16": "SELECT ANY_VALUE(1) AS col",
+                "postgres, version=17.5": "SELECT ANY_VALUE(1) AS col",
+            },
+        )
path: tests/dialects/test_postgres.py
text: @user1:
Nit: we can consolidate these in a single `validate_all` and move it under `test_postgres`.

@author:
Yeah wanted to mostly separate the two semantically, will do