summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-10 06:44:54 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-10 06:44:54 +0000
commitd2e9401b18925b5702c5c758af7d4f5b61deb493 (patch)
tree58dbf490c0457c2908751b3e4b63af13287381ee /tests/dialects/test_postgres.py
parentAdding upstream version 11.7.1. (diff)
downloadsqlglot-d2e9401b18925b5702c5c758af7d4f5b61deb493.tar.xz
sqlglot-d2e9401b18925b5702c5c758af7d4f5b61deb493.zip
Adding upstream version 12.2.0.upstream/12.2.0
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.py76
1 files changed, 60 insertions, 16 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index e2f9c41..b535a84 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -98,6 +98,21 @@ class TestPostgres(Validator):
self.validate_identity("STRING_AGG(x, ',' ORDER BY y DESC)")
self.validate_identity("STRING_AGG(DISTINCT x, ',' ORDER BY y DESC)")
self.validate_identity("SELECT CASE WHEN SUBSTRING('abcdefg') IN ('ab') THEN 1 ELSE 0 END")
+ 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 x WHERE SUBSTRING('Thomas' FROM '...$') IN ('mas')")
+ self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
+ self.validate_identity("SELECT TRIM(LEADING 'bla' FROM ' XXX ' COLLATE utf8_bin)")
+ self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
+ self.validate_identity("x ~ 'y'")
+ self.validate_identity("x ~* 'y'")
+ 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)"
+ )
+ self.validate_identity(
+ "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
+ )
self.validate_identity(
"SELECT CASE WHEN SUBSTRING('abcdefg' FROM 1) IN ('ab') THEN 1 ELSE 0 END"
)
@@ -107,37 +122,31 @@ class TestPostgres(Validator):
self.validate_identity(
'SELECT * FROM "x" WHERE SUBSTRING("x"."foo" FROM 1 FOR 2) IN (\'mas\')'
)
- self.validate_identity("SELECT * FROM x WHERE SUBSTRING('Thomas' FROM '...$') IN ('mas')")
self.validate_identity(
"SELECT * FROM x WHERE SUBSTRING('Thomas' FROM '%#\"o_a#\"_' FOR '#') IN ('mas')"
)
self.validate_identity(
"SELECT SUBSTRING('bla' + 'foo' || 'bar' FROM 3 - 1 + 5 FOR 4 + SOME_FUNC(arg1, arg2))"
)
- 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_identity("SELECT e'\\xDEADBEEF'")
- self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
+
+ self.validate_all(
+ "SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)",
+ write={
+ "databricks": "SELECT PERCENTILE_APPROX(amount, 0.5)",
+ "presto": "SELECT APPROX_PERCENTILE(amount, 0.5)",
+ "spark": "SELECT PERCENTILE_APPROX(amount, 0.5)",
+ "trino": "SELECT APPROX_PERCENTILE(amount, 0.5)",
+ },
+ )
self.validate_all(
"e'x'",
write={
"mysql": "x",
},
)
- self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
- 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)"
- )
- self.validate_identity(
- "CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
- )
- self.validate_identity("x ~ 'y'")
- self.validate_identity("x ~* 'y'")
-
self.validate_all(
"SELECT DATE_PART('isodow'::varchar(6), current_date)",
write={
@@ -198,6 +207,33 @@ class TestPostgres(Validator):
},
)
self.validate_all(
+ "GENERATE_SERIES(a, b)",
+ write={
+ "postgres": "GENERATE_SERIES(a, b)",
+ "presto": "SEQUENCE(a, b)",
+ "trino": "SEQUENCE(a, b)",
+ "tsql": "GENERATE_SERIES(a, b)",
+ },
+ )
+ self.validate_all(
+ "GENERATE_SERIES(a, b)",
+ read={
+ "postgres": "GENERATE_SERIES(a, b)",
+ "presto": "SEQUENCE(a, b)",
+ "trino": "SEQUENCE(a, b)",
+ "tsql": "GENERATE_SERIES(a, b)",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM t CROSS JOIN GENERATE_SERIES(2, 4)",
+ write={
+ "postgres": "SELECT * FROM t CROSS JOIN GENERATE_SERIES(2, 4)",
+ "presto": "SELECT * FROM t CROSS JOIN UNNEST(SEQUENCE(2, 4))",
+ "trino": "SELECT * FROM t CROSS JOIN UNNEST(SEQUENCE(2, 4))",
+ "tsql": "SELECT * FROM t CROSS JOIN GENERATE_SERIES(2, 4)",
+ },
+ )
+ self.validate_all(
"END WORK AND NO CHAIN",
write={"postgres": "COMMIT AND NO CHAIN"},
)
@@ -464,6 +500,14 @@ class TestPostgres(Validator):
},
)
+ self.validate_all(
+ "x / y ^ z",
+ write={
+ "": "x / POWER(y, z)",
+ "postgres": "x / y ^ z",
+ },
+ )
+
self.assertIsInstance(parse_one("id::UUID", read="postgres"), exp.TryCast)
def test_bool_or(self):