Merging upstream version 11.7.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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(