summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-11-01 05:12:38 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-11-01 05:12:38 +0000
commit613c2506f59fefeb0c0b921ca361c65e99458139 (patch)
treee1207c41ee9b35d77f1608e87e568fe60d701cf6 /tests/dialects/test_postgres.py
parentAdding upstream version 18.17.0. (diff)
downloadsqlglot-613c2506f59fefeb0c0b921ca361c65e99458139.tar.xz
sqlglot-613c2506f59fefeb0c0b921ca361c65e99458139.zip
Adding upstream version 19.0.1.upstream/19.0.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.py45
1 files changed, 37 insertions, 8 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 3121cb0..0e5f1a1 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -9,22 +9,39 @@ class TestPostgres(Validator):
dialect = "postgres"
def test_ddl(self):
- self.validate_identity(
- "CREATE INDEX foo ON bar.baz USING btree(col1 varchar_pattern_ops ASC, col2)"
- )
- self.validate_identity(
- "CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE[][])",
- "CREATE TABLE test (x TIMESTAMP[][])",
- )
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[])")
self.validate_identity("CREATE TABLE public.y (x TSTZRANGE NOT NULL)")
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("CREATE TABLE test (foo INT) PARTITION BY HASH(foo)")
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("UPDATE tbl_name SET foo = 123 RETURNING a")
+ self.validate_identity("CREATE TABLE cities_partdef PARTITION OF cities DEFAULT")
+ self.validate_identity(
+ "CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (MODULUS 3, REMAINDER 2)"
+ )
+ self.validate_identity(
+ "CREATE TABLE measurement_y2016m07 PARTITION OF measurement (unitsales DEFAULT 0) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')"
+ )
+ self.validate_identity(
+ "CREATE TABLE measurement_ym_older PARTITION OF measurement_year_month FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11)"
+ )
+ self.validate_identity(
+ "CREATE TABLE measurement_ym_y2016m11 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 11) TO (2016, 12)"
+ )
+ self.validate_identity(
+ "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b')"
+ )
+ self.validate_identity(
+ "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b') PARTITION BY RANGE(population)"
+ )
+ self.validate_identity(
+ "CREATE INDEX foo ON bar.baz USING btree(col1 varchar_pattern_ops ASC, col2)"
+ )
self.validate_identity(
"INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO NOTHING RETURNING *"
)
@@ -43,7 +60,10 @@ class TestPostgres(Validator):
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")
+ self.validate_identity(
+ "CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE[][])",
+ "CREATE TABLE test (x TIMESTAMP[][])",
+ )
self.validate_all(
"CREATE OR REPLACE FUNCTION function_name (input_a character varying DEFAULT NULL::character varying)",
@@ -187,6 +207,15 @@ class TestPostgres(Validator):
self.validate_identity(
"""ALTER TABLE ONLY "Album" ADD CONSTRAINT "FK_AlbumArtistId" FOREIGN KEY ("ArtistId") REFERENCES "Artist" ("ArtistId") ON DELETE RESTRICT"""
)
+ self.validate_identity(
+ "SELECT * FROM JSON_ARRAY_ELEMENTS('[1,true, [2,false]]') WITH ORDINALITY"
+ )
+ self.validate_identity(
+ "SELECT * FROM JSON_ARRAY_ELEMENTS('[1,true, [2,false]]') WITH ORDINALITY AS kv_json"
+ )
+ self.validate_identity(
+ "SELECT * FROM JSON_ARRAY_ELEMENTS('[1,true, [2,false]]') WITH ORDINALITY AS kv_json(a, b)"
+ )
self.validate_identity("x @@ y")
self.validate_identity("CAST(x AS MONEY)")
self.validate_identity("CAST(x AS INT4RANGE)")