summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2022-10-15 13:52:53 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2022-10-15 13:52:53 +0000
commit97d3673ec2d668050912aa6aea1816885ca6c5ab (patch)
treef391e30e039a3d22368e9696e171f759e104c765 /tests/dialects/test_postgres.py
parentAdding upstream version 6.3.1. (diff)
downloadsqlglot-97d3673ec2d668050912aa6aea1816885ca6c5ab.tar.xz
sqlglot-97d3673ec2d668050912aa6aea1816885ca6c5ab.zip
Adding upstream version 7.1.3.upstream/7.1.3
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_postgres.py')
-rw-r--r--tests/dialects/test_postgres.py41
1 files changed, 41 insertions, 0 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index dc93c3a..4b8f3c3 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -68,10 +68,12 @@ class TestPostgres(Validator):
self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
self.validate_identity("SELECT TRIM(LEADING 'bla' FROM ' XXX ' COLLATE utf8_bin)")
self.validate_identity("SELECT TO_TIMESTAMP(1284352323.5), TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY')")
+ self.validate_identity("COMMENT ON TABLE mytable IS 'this'")
self.validate_all(
"CREATE TABLE x (a UUID, b BYTEA)",
write={
+ "duckdb": "CREATE TABLE x (a UUID, b BINARY)",
"presto": "CREATE TABLE x (a UUID, b VARBINARY)",
"hive": "CREATE TABLE x (a UUID, b BINARY)",
"spark": "CREATE TABLE x (a UUID, b BINARY)",
@@ -163,3 +165,42 @@ class TestPostgres(Validator):
"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",
},
)
+ self.validate_all(
+ "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE NOT deleted IS NULL",
+ read={"postgres": "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE deleted NOTNULL"},
+ )
+ self.validate_all(
+ "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE NOT deleted IS NULL",
+ read={"postgres": "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE NOT deleted ISNULL"},
+ )
+ self.validate_all(
+ "'[1,2,3]'::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'"""},
+ )
+ self.validate_all(
+ """'{"x": {"y": 1}}'::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'"""},
+ )
+ self.validate_all(
+ """'[1,2,3]'::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'"""},
+ )
+ 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}'"""},
+ )
+ 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}'"""},
+ )