diff options
Diffstat (limited to 'tests/dialects/test_postgres.py')
| -rw-r--r-- | tests/dialects/test_postgres.py | 45 |
1 files changed, 45 insertions, 0 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index d1ecb2a..1d0ea8b 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -8,8 +8,10 @@ class TestPostgres(Validator): dialect = "postgres" def test_postgres(self): + self.validate_identity("1.x", "1. AS x") self.validate_identity("|/ x", "SQRT(x)") self.validate_identity("||/ x", "CBRT(x)") + expr = parse_one( "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)", read="postgres" ) @@ -82,6 +84,7 @@ class TestPostgres(Validator): self.validate_identity("CAST(1 AS DECIMAL) / CAST(2 AS DECIMAL) * -100") self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True) self.validate_identity("SELECT CURRENT_USER") + self.validate_identity("SELECT * FROM ONLY t1") self.validate_identity( """LAST_VALUE("col1") OVER (ORDER BY "col2" RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND '1 month' FOLLOWING)""" ) @@ -164,6 +167,9 @@ class TestPostgres(Validator): "SELECT 'Dianne''s horse'", ) self.validate_identity( + "COMMENT ON TABLE mytable IS $$doc this$$", "COMMENT ON TABLE mytable IS 'doc this'" + ) + self.validate_identity( "UPDATE MYTABLE T1 SET T1.COL = 13", "UPDATE MYTABLE AS T1 SET T1.COL = 13", ) @@ -320,6 +326,7 @@ class TestPostgres(Validator): "MERGE INTO x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET x.a = y.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (y.a, y.b)", "MERGE INTO x USING (SELECT id) AS y ON a = b WHEN MATCHED THEN UPDATE SET a = y.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (y.a, y.b)", ) + self.validate_identity("SELECT * FROM t1*", "SELECT * FROM t1") self.validate_all( "SELECT JSON_EXTRACT_PATH_TEXT(x, k1, k2, k3) FROM t", @@ -653,6 +660,12 @@ class TestPostgres(Validator): self.validate_identity("CREATE TABLE t (c CHAR(2) UNIQUE NOT NULL) INHERITS (t1)") self.validate_identity("CREATE TABLE s.t (c CHAR(2) UNIQUE NOT NULL) INHERITS (s.t1, s.t2)") self.validate_identity("CREATE FUNCTION x(INT) RETURNS INT SET search_path = 'public'") + self.validate_identity("TRUNCATE TABLE t1 CONTINUE IDENTITY") + self.validate_identity("TRUNCATE TABLE t1 RESTART IDENTITY") + self.validate_identity("TRUNCATE TABLE t1 CASCADE") + self.validate_identity("TRUNCATE TABLE t1 RESTRICT") + self.validate_identity("TRUNCATE TABLE t1 CONTINUE IDENTITY CASCADE") + self.validate_identity("TRUNCATE TABLE t1 RESTART IDENTITY RESTRICT") self.validate_identity( "CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (MODULUS 3, REMAINDER 2)" ) @@ -785,6 +798,10 @@ class TestPostgres(Validator): self.validate_identity( "CREATE INDEX index_ci_pipelines_on_project_idandrefandiddesc ON public.ci_pipelines USING btree(project_id, ref, id DESC)" ) + self.validate_identity( + "TRUNCATE TABLE ONLY t1, t2*, ONLY t3, t4, t5* RESTART IDENTITY CASCADE", + "TRUNCATE TABLE ONLY t1, t2, ONLY t3, t4, t5 RESTART IDENTITY CASCADE", + ) with self.assertRaises(ParseError): transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres") @@ -911,3 +928,31 @@ class TestPostgres(Validator): """See https://github.com/tobymao/sqlglot/pull/2404 for details.""" self.assertIsInstance(parse_one("'thomas' ~ '.*thomas.*'", read="postgres"), exp.Binary) self.assertIsInstance(parse_one("'thomas' ~* '.*thomas.*'", read="postgres"), exp.Binary) + + def test_unnest_json_array(self): + trino_input = """ + WITH t(boxcrate) AS ( + SELECT JSON '[{"boxes": [{"name": "f1", "type": "plant", "color": "red"}]}]' + ) + SELECT + JSON_EXTRACT_SCALAR(boxes,'$.name') AS name, + JSON_EXTRACT_SCALAR(boxes,'$.type') AS type, + JSON_EXTRACT_SCALAR(boxes,'$.color') AS color + FROM t + CROSS JOIN UNNEST(CAST(boxcrate AS array(json))) AS x(tbox) + CROSS JOIN UNNEST(CAST(json_extract(tbox, '$.boxes') AS array(json))) AS y(boxes) + """ + + expected_postgres = """WITH t(boxcrate) AS ( + SELECT + CAST('[{"boxes": [{"name": "f1", "type": "plant", "color": "red"}]}]' AS JSON) +) +SELECT + JSON_EXTRACT_PATH_TEXT(boxes, 'name') AS name, + JSON_EXTRACT_PATH_TEXT(boxes, 'type') AS type, + JSON_EXTRACT_PATH_TEXT(boxes, 'color') AS color +FROM t +CROSS JOIN JSON_ARRAY_ELEMENTS(CAST(boxcrate AS JSON)) AS x(tbox) +CROSS JOIN JSON_ARRAY_ELEMENTS(CAST(JSON_EXTRACT_PATH(tbox, 'boxes') AS JSON)) AS y(boxes)""" + + self.validate_all(expected_postgres, read={"trino": trino_input}, pretty=True) |