summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-03 09:12:24 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-03 09:12:24 +0000
commit98d5537435b2951b36c45f1fda667fa27c165794 (patch)
treed26b4dfa6cf91847100fe10a94a04dcc2ad36a86 /tests/dialects/test_postgres.py
parentAdding upstream version 11.5.2. (diff)
downloadsqlglot-98d5537435b2951b36c45f1fda667fa27c165794.tar.xz
sqlglot-98d5537435b2951b36c45f1fda667fa27c165794.zip
Adding upstream version 11.7.1.upstream/11.7.1
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.py94
1 files changed, 85 insertions, 9 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index a89ae30..e2f9c41 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1,4 +1,4 @@
-from sqlglot import ParseError, transpile
+from sqlglot import ParseError, exp, parse_one, transpile
from tests.dialects.test_dialect import Validator
@@ -10,11 +10,25 @@ class TestPostgres(Validator):
self.validate_identity("CREATE TABLE test (foo HSTORE)")
self.validate_identity("CREATE TABLE test (foo JSONB)")
self.validate_identity("CREATE TABLE test (foo VARCHAR(64)[])")
-
self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a")
self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a, b")
self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING *")
self.validate_identity(
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO NOTHING RETURNING *"
+ )
+ self.validate_identity(
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = 1 RETURNING *"
+ )
+ self.validate_identity(
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = excluded.id RETURNING *"
+ )
+ self.validate_identity(
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT ON CONSTRAINT pkey DO NOTHING RETURNING *"
+ )
+ self.validate_identity(
+ "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT ON CONSTRAINT pkey DO UPDATE SET x.id = 1 RETURNING *"
+ )
+ self.validate_identity(
"DELETE FROM event USING sales AS s WHERE event.eventid = s.eventid RETURNING a"
)
self.validate_identity("UPDATE tbl_name SET foo = 123 RETURNING a")
@@ -75,6 +89,7 @@ class TestPostgres(Validator):
self.validate_identity("SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]")
self.validate_identity("SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]")
self.validate_identity("$x")
+ self.validate_identity("x$")
self.validate_identity("SELECT ARRAY[1, 2, 3]")
self.validate_identity("SELECT ARRAY(SELECT 1)")
self.validate_identity("SELECT ARRAY_LENGTH(ARRAY[1, 2, 3], 1)")
@@ -107,6 +122,12 @@ 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_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)"
@@ -118,6 +139,28 @@ class TestPostgres(Validator):
self.validate_identity("x ~* 'y'")
self.validate_all(
+ "SELECT DATE_PART('isodow'::varchar(6), current_date)",
+ write={
+ "postgres": "SELECT EXTRACT(CAST('isodow' AS VARCHAR(6)) FROM CURRENT_DATE)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATE_PART('minute', timestamp '2023-01-04 04:05:06.789')",
+ write={
+ "postgres": "SELECT EXTRACT(minute FROM CAST('2023-01-04 04:05:06.789' AS TIMESTAMP))",
+ "redshift": "SELECT EXTRACT(minute FROM CAST('2023-01-04 04:05:06.789' AS TIMESTAMP))",
+ "snowflake": "SELECT EXTRACT(minute FROM CAST('2023-01-04 04:05:06.789' AS TIMESTAMPNTZ))",
+ },
+ )
+ self.validate_all(
+ "SELECT DATE_PART('month', date '20220502')",
+ write={
+ "postgres": "SELECT EXTRACT(month FROM CAST('20220502' AS DATE))",
+ "redshift": "SELECT EXTRACT(month FROM CAST('20220502' AS DATE))",
+ "snowflake": "SELECT EXTRACT(month FROM CAST('20220502' AS DATE))",
+ },
+ )
+ self.validate_all(
"SELECT (DATE '2016-01-10', DATE '2016-02-01') OVERLAPS (DATE '2016-01-20', DATE '2016-02-10')",
write={
"postgres": "SELECT (CAST('2016-01-10' AS DATE), CAST('2016-02-01' AS DATE)) OVERLAPS (CAST('2016-01-20' AS DATE), CAST('2016-02-10' AS DATE))",
@@ -141,17 +184,17 @@ class TestPostgres(Validator):
self.validate_all(
"GENERATE_SERIES(a, b, ' 2 days ')",
write={
- "postgres": "GENERATE_SERIES(a, b, INTERVAL '2' days)",
- "presto": "SEQUENCE(a, b, INTERVAL '2' days)",
- "trino": "SEQUENCE(a, b, INTERVAL '2' days)",
+ "postgres": "GENERATE_SERIES(a, b, INTERVAL '2' day)",
+ "presto": "SEQUENCE(a, b, INTERVAL '2' day)",
+ "trino": "SEQUENCE(a, b, INTERVAL '2' day)",
},
)
self.validate_all(
"GENERATE_SERIES('2019-01-01'::TIMESTAMP, NOW(), '1day')",
write={
"postgres": "GENERATE_SERIES(CAST('2019-01-01' AS TIMESTAMP), CURRENT_TIMESTAMP, INTERVAL '1' day)",
- "presto": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' day)",
- "trino": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' day)",
+ "presto": "SEQUENCE(TRY_CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' day)",
+ "trino": "SEQUENCE(TRY_CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' day)",
},
)
self.validate_all(
@@ -296,7 +339,10 @@ class TestPostgres(Validator):
)
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'""",
+ "redshift": """CAST('{"a":1,"b":2}' AS JSON)."b\"""",
+ },
)
self.validate_all(
"""'{"x": {"y": 1}}'::json->'x'->'y'""",
@@ -326,7 +372,7 @@ class TestPostgres(Validator):
"""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""",
+ "presto": """SELECT JSON_ARRAY_ELEMENTS(TRY_CAST((JSON_EXTRACT(foo, 'sections')) AS JSON)) AS sections""",
},
)
self.validate_all(
@@ -389,6 +435,36 @@ class TestPostgres(Validator):
"spark": "TRIM(BOTH 'as' FROM 'as string as')",
},
)
+ self.validate_all(
+ "merge into x as x using (select id) as y on a = b WHEN matched then update set X.a = y.b",
+ write={
+ "postgres": "MERGE INTO x AS x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b",
+ "snowflake": "MERGE INTO x AS x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET X.a = y.b",
+ },
+ )
+ self.validate_all(
+ "merge into x as z using (select id) as y on a = b WHEN matched then update set X.a = y.b",
+ write={
+ "postgres": "MERGE INTO x AS z USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b",
+ "snowflake": "MERGE INTO x AS z USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET X.a = y.b",
+ },
+ )
+ self.validate_all(
+ "merge into x as z using (select id) as y on a = b WHEN matched then update set Z.a = y.b",
+ write={
+ "postgres": "MERGE INTO x AS z USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b",
+ "snowflake": "MERGE INTO x AS z USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET Z.a = y.b",
+ },
+ )
+ self.validate_all(
+ "merge into x using (select id) as y on a = b WHEN matched then update set x.a = y.b",
+ write={
+ "postgres": "MERGE INTO x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b",
+ "snowflake": "MERGE INTO x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET x.a = y.b",
+ },
+ )
+
+ self.assertIsInstance(parse_one("id::UUID", read="postgres"), exp.TryCast)
def test_bool_or(self):
self.validate_all(