summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects/test_postgres.py')
-rw-r--r--tests/dialects/test_postgres.py97
1 files changed, 85 insertions, 12 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 962b28b..1e048d5 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -3,6 +3,7 @@ from tests.dialects.test_dialect import Validator
class TestPostgres(Validator):
+ maxDiff = None
dialect = "postgres"
def test_ddl(self):
@@ -94,6 +95,7 @@ class TestPostgres(Validator):
self.validate_identity("COMMENT ON TABLE mytable IS 'this'")
self.validate_identity("SELECT e'\\xDEADBEEF'")
self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
+ self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
self.validate_all(
"END WORK AND NO CHAIN",
@@ -112,6 +114,14 @@ class TestPostgres(Validator):
"spark": "CREATE TABLE x (a UUID, b BINARY)",
},
)
+ self.validate_all(
+ "123::CHARACTER VARYING",
+ write={"postgres": "CAST(123 AS VARCHAR)"},
+ )
+ self.validate_all(
+ "TO_TIMESTAMP(123::DOUBLE PRECISION)",
+ write={"postgres": "TO_TIMESTAMP(CAST(123 AS DOUBLE PRECISION))"},
+ )
self.validate_identity(
"CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
@@ -193,15 +203,21 @@ class TestPostgres(Validator):
},
)
self.validate_all(
- "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
- read={
+ "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) pname ON TRUE WHERE pname IS NULL",
+ write={
"postgres": "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
},
)
self.validate_all(
"SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
- read={
- "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons p1, polygons p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id",
+ write={
+ "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) AS v1, LATERAL VERTICES(p2.poly) AS v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM r CROSS JOIN LATERAL unnest(array(1)) AS s(location)",
+ write={
+ "postgres": "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)",
},
)
self.validate_all(
@@ -218,35 +234,46 @@ class TestPostgres(Validator):
)
self.validate_all(
"'[1,2,3]'::json->2",
- write={"postgres": "CAST('[1,2,3]' AS JSON)->'2'"},
+ write={"postgres": "CAST('[1,2,3]' AS JSON) -> '2'"},
)
self.validate_all(
"""'{"a":1,"b":2}'::json->'b'""",
- write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->'b'"""},
+ write={"postgres": """CAST('{"a":1,"b":2}' AS JSON) -> 'b'"""},
)
self.validate_all(
"""'{"x": {"y": 1}}'::json->'x'->'y'""",
- write={"postgres": """CAST('{"x": {"y": 1}}' AS JSON)->'x'->'y'"""},
+ write={"postgres": """CAST('{"x": {"y": 1}}' AS JSON) -> 'x' -> 'y'"""},
)
self.validate_all(
"""'{"x": {"y": 1}}'::json->'x'::json->'y'""",
- write={"postgres": """CAST(CAST('{"x": {"y": 1}}' AS JSON)->'x' AS JSON)->'y'"""},
+ write={"postgres": """CAST(CAST('{"x": {"y": 1}}' AS JSON) -> 'x' AS JSON) -> 'y'"""},
)
self.validate_all(
"""'[1,2,3]'::json->>2""",
- write={"postgres": "CAST('[1,2,3]' AS JSON)->>'2'"},
+ write={"postgres": "CAST('[1,2,3]' AS JSON) ->> '2'"},
)
self.validate_all(
"""'{"a":1,"b":2}'::json->>'b'""",
- write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->>'b'"""},
+ write={"postgres": """CAST('{"a":1,"b":2}' AS JSON) ->> 'b'"""},
)
self.validate_all(
"""'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'""",
- write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON)#>'{a,2}'"""},
+ write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON) #> '{a,2}'"""},
)
self.validate_all(
"""'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'""",
- write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON)#>>'{a,2}'"""},
+ write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON) #>> '{a,2}'"""},
+ )
+ self.validate_all(
+ """SELECT JSON_ARRAY_ELEMENTS((foo->'sections')::JSON) AS sections""",
+ write={
+ "postgres": """SELECT JSON_ARRAY_ELEMENTS(CAST((foo -> 'sections') AS JSON)) AS sections""",
+ "presto": """SELECT JSON_ARRAY_ELEMENTS(CAST((JSON_EXTRACT(foo, 'sections')) AS JSON)) AS sections""",
+ },
+ )
+ self.validate_all(
+ """x ? 'x'""",
+ write={"postgres": "x ? 'x'"},
)
self.validate_all(
"SELECT $$a$$",
@@ -260,3 +287,49 @@ class TestPostgres(Validator):
"UPDATE MYTABLE T1 SET T1.COL = 13",
write={"postgres": "UPDATE MYTABLE AS T1 SET T1.COL = 13"},
)
+
+ self.validate_identity("x ~ 'y'")
+ self.validate_identity("x ~* 'y'")
+ self.validate_all(
+ "x !~ 'y'",
+ write={"postgres": "NOT x ~ 'y'"},
+ )
+ self.validate_all(
+ "x !~* 'y'",
+ write={"postgres": "NOT x ~* 'y'"},
+ )
+
+ self.validate_all(
+ "x ~~ 'y'",
+ write={"postgres": "x LIKE 'y'"},
+ )
+ self.validate_all(
+ "x ~~* 'y'",
+ write={"postgres": "x ILIKE 'y'"},
+ )
+ self.validate_all(
+ "x !~~ 'y'",
+ write={"postgres": "NOT x LIKE 'y'"},
+ )
+ self.validate_all(
+ "x !~~* 'y'",
+ write={"postgres": "NOT x ILIKE 'y'"},
+ )
+ self.validate_all(
+ "'45 days'::interval day",
+ write={"postgres": "CAST('45 days' AS INTERVAL day)"},
+ )
+ self.validate_all(
+ "'x' 'y' 'z'",
+ write={"postgres": "CONCAT('x', 'y', 'z')"},
+ )
+ self.validate_identity("SELECT ARRAY(SELECT 1)")
+
+ self.validate_all(
+ "x::cstring",
+ write={"postgres": "CAST(x AS CSTRING)"},
+ )
+
+ self.validate_identity(
+ "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+ )
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-09 02:00:01 +0000