1 files changed, 56 insertions, 0 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 972a8c8..4e57b36 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1,3 +1,5 @@
+from unittest import mock
+
 from sqlglot import ParseError, exp, parse_one, transpile
 from tests.dialects.test_dialect import Validator
 
@@ -85,6 +87,39 @@ class TestPostgres(Validator):
                 read="postgres",
             )
 
+    def test_unnest(self):
+        self.validate_identity(
+            "SELECT * FROM UNNEST(ARRAY[1, 2], ARRAY['foo', 'bar', 'baz']) AS x(a, b)"
+        )
+
+        self.validate_all(
+            "SELECT UNNEST(c) FROM t",
+            write={
+                "hive": "SELECT EXPLODE(c) FROM t",
+                "postgres": "SELECT UNNEST(c) FROM t",
+                "presto": "SELECT col FROM t CROSS JOIN UNNEST(c) AS _u(col)",
+            },
+        )
+        self.validate_all(
+            "SELECT UNNEST(ARRAY[1])",
+            write={
+                "hive": "SELECT EXPLODE(ARRAY(1))",
+                "postgres": "SELECT UNNEST(ARRAY[1])",
+                "presto": "SELECT col FROM UNNEST(ARRAY[1]) AS _u(col)",
+            },
+        )
+
+    @mock.patch("sqlglot.helper.logger")
+    def test_array_offset(self, mock_logger):
+        self.validate_all(
+            "SELECT col[1]",
+            write={
+                "hive": "SELECT col[0]",
+                "postgres": "SELECT col[1]",
+                "presto": "SELECT col[1]",
+            },
+        )
+
     def test_postgres(self):
         self.validate_identity("CAST(x AS INT4RANGE)")
         self.validate_identity("CAST(x AS INT4MULTIRANGE)")
@@ -540,3 +575,24 @@ class TestPostgres(Validator):
             "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
             write={"postgres": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
         )
+
+    def test_string_concat(self):
+        self.validate_all(
+            "CONCAT(a, b)",
+            write={
+                "": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
+                "duckdb": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
+                "postgres": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
+                "presto": "CONCAT(CAST(COALESCE(a, '') AS VARCHAR), CAST(COALESCE(b, '') AS VARCHAR))",
+            },
+        )
+        self.validate_all(
+            "a || b",
+            write={
+                "": "a || b",
+                "clickhouse": "CONCAT(CAST(a AS TEXT), CAST(b AS TEXT))",
+                "duckdb": "a || b",
+                "postgres": "a || b",
+                "presto": "CONCAT(CAST(a AS VARCHAR), CAST(b AS VARCHAR))",
+            },
+        )