diff options
Diffstat (limited to 'tests/dialects/test_postgres.py')
| -rw-r--r-- | tests/dialects/test_postgres.py | 58 |
1 files changed, 49 insertions, 9 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index 5a55a7d..a8a6c12 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -312,9 +312,33 @@ 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_identity( + "SELECT * FROM t1*", + "SELECT * FROM t1", + ) + self.validate_identity( + "SELECT SUBSTRING('afafa' for 1)", + "SELECT SUBSTRING('afafa' FROM 1 FOR 1)", + ) + self.validate_identity( + "CAST(x AS INT8)", + "CAST(x AS BIGINT)", + ) self.validate_all( + "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')", + write={ + "duckdb": "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')", + "postgres": "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')", + }, + ) + self.validate_all( + "CREATE TABLE t (c INT)", + read={ + "mysql": "CREATE TABLE t (c INT COMMENT 'comment 1') COMMENT = 'comment 2'", + }, + ) + self.validate_all( 'SELECT * FROM "test_table" ORDER BY RANDOM() LIMIT 5', write={ "bigquery": "SELECT * FROM `test_table` ORDER BY RAND() NULLS LAST LIMIT 5", @@ -449,7 +473,7 @@ class TestPostgres(Validator): 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 DATE_PART(minute, CAST('2023-01-04 04:05:06.789' AS TIMESTAMPNTZ))", + "snowflake": "SELECT DATE_PART(minute, CAST('2023-01-04 04:05:06.789' AS TIMESTAMP))", }, ) self.validate_all( @@ -660,6 +684,16 @@ class TestPostgres(Validator): ) self.assertIsInstance(self.parse_one("id::UUID"), exp.Cast) + self.validate_identity( + "COPY tbl (col1, col2) FROM 'file' WITH (FORMAT format, HEADER MATCH, FREEZE TRUE)" + ) + self.validate_identity( + "COPY tbl (col1, col2) TO 'file' WITH (FORMAT format, HEADER MATCH, FREEZE TRUE)" + ) + self.validate_identity( + "COPY (SELECT * FROM t) TO 'file' WITH (FORMAT format, HEADER MATCH, FREEZE TRUE)" + ) + def test_ddl(self): # Checks that user-defined types are parsed into DataType instead of Identifier self.parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is( @@ -676,6 +710,7 @@ class TestPostgres(Validator): cdef.args["kind"].assert_is(exp.DataType) self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL DAY)") + self.validate_identity("CREATE INDEX IF NOT EXISTS ON t(c)") self.validate_identity("CREATE INDEX et_vid_idx ON et(vid) INCLUDE (fid)") self.validate_identity("CREATE INDEX idx_x ON x USING BTREE(x, y) WHERE (NOT y IS NULL)") self.validate_identity("CREATE TABLE test (elems JSONB[])") @@ -698,6 +733,13 @@ class TestPostgres(Validator): 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("ALTER TABLE t1 SET LOGGED") + self.validate_identity("ALTER TABLE t1 SET UNLOGGED") + self.validate_identity("ALTER TABLE t1 SET WITHOUT CLUSTER") + self.validate_identity("ALTER TABLE t1 SET WITHOUT OIDS") + self.validate_identity("ALTER TABLE t1 SET ACCESS METHOD method") + self.validate_identity("ALTER TABLE t1 SET TABLESPACE tablespace") + self.validate_identity("ALTER TABLE t1 SET (fillfactor = 5, autovacuum_enabled = TRUE)") self.validate_identity( "CREATE TABLE t (vid INT NOT NULL, CONSTRAINT ht_vid_nid_fid_idx EXCLUDE (INT4RANGE(vid, nid) WITH &&, INT4RANGE(fid, fid, '[]') WITH &&))" ) @@ -763,23 +805,21 @@ class TestPostgres(Validator): "CREATE TABLE test (x TIMESTAMP[][])", ) self.validate_identity( - "CREATE FUNCTION add(INT, INT) RETURNS INT SET search_path TO 'public' AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE", - check_command_warning=True, + "CREATE FUNCTION add(integer, integer) RETURNS INT LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT AS 'select $1 + $2;'", ) self.validate_identity( - "CREATE FUNCTION x(INT) RETURNS INT SET foo FROM CURRENT", - check_command_warning=True, + "CREATE FUNCTION add(integer, integer) RETURNS INT LANGUAGE SQL IMMUTABLE STRICT AS 'select $1 + $2;'" ) self.validate_identity( - "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT", + "CREATE FUNCTION add(INT, INT) RETURNS INT SET search_path TO 'public' AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE", check_command_warning=True, ) self.validate_identity( - "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE CALLED ON NULL INPUT", + "CREATE FUNCTION x(INT) RETURNS INT SET foo FROM CURRENT", check_command_warning=True, ) self.validate_identity( - "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE STRICT", + "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE CALLED ON NULL INPUT", check_command_warning=True, ) self.validate_identity( |