summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--tests/dialects/test_postgres.py56
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))",
+ },
+ )