diff options
Diffstat (limited to 'tests/dialects/test_postgres.py')
| -rw-r--r-- | tests/dialects/test_postgres.py | 94 |
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( |