summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-09-25 08:20:06 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-09-25 08:20:06 +0000
commitbd2d949d1f2fb728cf4c429dd3ae9a1510e10182 (patch)
treec051102e5aff0ca2d75e5b96b09968c52114060a /tests/dialects/test_postgres.py
parentAdding upstream version 18.5.1. (diff)
downloadsqlglot-bd2d949d1f2fb728cf4c429dd3ae9a1510e10182.tar.xz
sqlglot-bd2d949d1f2fb728cf4c429dd3ae9a1510e10182.zip
Adding upstream version 18.7.0.upstream/18.7.0
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, 39 insertions, 6 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 285496a..6a3df47 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -13,6 +13,7 @@ class TestPostgres(Validator):
"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)")
@@ -83,6 +84,28 @@ class TestPostgres(Validator):
" CONSTRAINT valid_discount CHECK (price > discounted_price))"
},
)
+ self.validate_identity(
+ """
+ CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial
+ ON public.ci_builds
+ USING btree (commit_id, artifacts_expire_at, id)
+ WHERE (
+ ((type)::text = 'Ci::Build'::text)
+ AND ((retried = false) OR (retried IS NULL))
+ AND ((name)::text = ANY (ARRAY[
+ ('sast'::character varying)::text,
+ ('dependency_scanning'::character varying)::text,
+ ('sast:container'::character varying)::text,
+ ('container_scanning'::character varying)::text,
+ ('dast'::character varying)::text
+ ]))
+ )
+ """,
+ "CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial ON public.ci_builds USING btree(commit_id, artifacts_expire_at, id) WHERE ((CAST((type) AS TEXT) = CAST('Ci::Build' AS TEXT)) AND ((retried = FALSE) OR (retried IS NULL)) AND (CAST((name) AS TEXT) = ANY (ARRAY[CAST((CAST('sast' AS VARCHAR)) AS TEXT), CAST((CAST('dependency_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('sast:container' AS VARCHAR)) AS TEXT), CAST((CAST('container_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('dast' AS VARCHAR)) AS TEXT)])))",
+ )
+ self.validate_identity(
+ "CREATE INDEX index_ci_pipelines_on_project_idandrefandiddesc ON public.ci_pipelines USING btree(project_id, ref, id DESC)"
+ )
with self.assertRaises(ParseError):
transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres")
@@ -102,7 +125,7 @@ class TestPostgres(Validator):
write={
"hive": "SELECT EXPLODE(c) FROM t",
"postgres": "SELECT UNNEST(c) FROM t",
- "presto": "SELECT col FROM t CROSS JOIN UNNEST(c) AS _u(col)",
+ "presto": "SELECT IF(pos = pos_2, col) AS col FROM t, UNNEST(SEQUENCE(1, GREATEST(CARDINALITY(c)))) AS _u(pos) CROSS JOIN UNNEST(c) WITH ORDINALITY AS _u_2(col, pos_2) WHERE pos = pos_2 OR (pos > CARDINALITY(c) AND pos_2 = CARDINALITY(c))",
},
)
self.validate_all(
@@ -110,7 +133,7 @@ class TestPostgres(Validator):
write={
"hive": "SELECT EXPLODE(ARRAY(1))",
"postgres": "SELECT UNNEST(ARRAY[1])",
- "presto": "SELECT col FROM UNNEST(ARRAY[1]) AS _u(col)",
+ "presto": "SELECT IF(pos = pos_2, col) AS col FROM UNNEST(SEQUENCE(1, GREATEST(CARDINALITY(ARRAY[1])))) AS _u(pos) CROSS JOIN UNNEST(ARRAY[1]) WITH ORDINALITY AS _u_2(col, pos_2) WHERE pos = pos_2 OR (pos > CARDINALITY(ARRAY[1]) AND pos_2 = CARDINALITY(ARRAY[1]))",
},
)
@@ -139,6 +162,16 @@ class TestPostgres(Validator):
self.assertIsInstance(expr, exp.AlterTable)
self.assertEqual(expr.sql(dialect="postgres"), alter_table_only)
+ self.validate_identity(
+ "SELECT ARRAY[]::INT[] AS foo",
+ "SELECT CAST(ARRAY[] AS INT[]) AS foo",
+ )
+ self.validate_identity(
+ """ALTER TABLE ONLY "Album" ADD CONSTRAINT "FK_AlbumArtistId" FOREIGN KEY ("ArtistId") REFERENCES "Artist" ("ArtistId") ON DELETE CASCADE"""
+ )
+ self.validate_identity(
+ """ALTER TABLE ONLY "Album" ADD CONSTRAINT "FK_AlbumArtistId" FOREIGN KEY ("ArtistId") REFERENCES "Artist" ("ArtistId") ON DELETE RESTRICT"""
+ )
self.validate_identity("x @@ y")
self.validate_identity("CAST(x AS MONEY)")
self.validate_identity("CAST(x AS INT4RANGE)")
@@ -362,10 +395,10 @@ class TestPostgres(Validator):
self.validate_all(
"SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
write={
- "postgres": "SELECT fname, lname, age FROM person ORDER BY age DESC, fname, lname",
- "presto": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname, lname",
- "hive": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname NULLS LAST, lname NULLS LAST",
- "spark": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname NULLS LAST, lname NULLS LAST",
+ "postgres": "SELECT fname, lname, age FROM person ORDER BY age DESC, fname ASC, lname",
+ "presto": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC, lname",
+ "hive": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname NULLS LAST",
+ "spark": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname NULLS LAST",
},
)
self.validate_all(