summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_postgres.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2022-09-22 04:30:57 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2022-09-22 04:30:57 +0000
commitd6dd6db5060d5d7a4abbc132cb1240973e6f9487 (patch)
tree5444ff47b199f16fc23d44efc523fea23a5a1132 /tests/dialects/test_postgres.py
parentAdding upstream version 6.0.4. (diff)
downloadsqlglot-d6dd6db5060d5d7a4abbc132cb1240973e6f9487.tar.xz
sqlglot-d6dd6db5060d5d7a4abbc132cb1240973e6f9487.zip
Adding upstream version 6.1.1.upstream/6.1.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.py93
1 files changed, 82 insertions, 11 deletions
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 15dbfd0..e0934d7 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -8,9 +8,7 @@ class TestPostgres(Validator):
def test_ddl(self):
self.validate_all(
"CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)",
- write={
- "postgres": "CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)"
- },
+ write={"postgres": "CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)"},
)
self.validate_all(
"CREATE TABLE products (product_no INT CONSTRAINT must_be_different UNIQUE, name TEXT CONSTRAINT present NOT NULL, price DECIMAL)",
@@ -42,11 +40,17 @@ class TestPostgres(Validator):
" CONSTRAINT valid_discount CHECK (price > discounted_price))"
},
)
+ self.validate_all(
+ "CREATE TABLE products (x INT GENERATED BY DEFAULT AS IDENTITY)",
+ write={"postgres": "CREATE TABLE products (x INT GENERATED BY DEFAULT AS IDENTITY)"},
+ )
+ self.validate_all(
+ "CREATE TABLE products (x INT GENERATED ALWAYS AS IDENTITY)",
+ write={"postgres": "CREATE TABLE products (x INT GENERATED ALWAYS AS IDENTITY)"},
+ )
with self.assertRaises(ParseError):
- transpile(
- "CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres"
- )
+ transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres")
with self.assertRaises(ParseError):
transpile(
"CREATE TABLE products (price DECIMAL, CHECK price > 1)",
@@ -54,11 +58,16 @@ class TestPostgres(Validator):
)
def test_postgres(self):
- self.validate_all(
- "CREATE TABLE x (a INT SERIAL)",
- read={"sqlite": "CREATE TABLE x (a INTEGER AUTOINCREMENT)"},
- write={"sqlite": "CREATE TABLE x (a INTEGER AUTOINCREMENT)"},
- )
+ self.validate_identity("SELECT CASE WHEN SUBSTRING('abcdefg') IN ('ab') THEN 1 ELSE 0 END")
+ self.validate_identity("SELECT CASE WHEN SUBSTRING('abcdefg' FROM 1) IN ('ab') THEN 1 ELSE 0 END")
+ self.validate_identity("SELECT CASE WHEN SUBSTRING('abcdefg' FROM 1 FOR 2) IN ('ab') THEN 1 ELSE 0 END")
+ self.validate_identity('SELECT * FROM "x" WHERE SUBSTRING("x"."foo" FROM 1 FOR 2) IN (\'mas\')')
+ self.validate_identity("SELECT * FROM x WHERE SUBSTRING('Thomas' FROM '...$') IN ('mas')")
+ self.validate_identity("SELECT * FROM x WHERE SUBSTRING('Thomas' FROM '%#\"o_a#\"_' FOR '#') IN ('mas')")
+ self.validate_identity("SELECT SUBSTRING('bla' + 'foo' || 'bar' FROM 3 - 1 + 5 FOR 4 + SOME_FUNC(arg1, arg2))")
+ self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
+ self.validate_identity("SELECT TRIM(LEADING 'bla' FROM ' XXX ' COLLATE utf8_bin)")
+
self.validate_all(
"CREATE TABLE x (a UUID, b BYTEA)",
write={
@@ -91,3 +100,65 @@ class TestPostgres(Validator):
"spark": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname NULLS LAST, lname NULLS LAST",
},
)
+ self.validate_all(
+ "SELECT CASE WHEN SUBSTRING('abcdefg' FROM 1 FOR 2) IN ('ab') THEN 1 ELSE 0 END",
+ write={
+ "hive": "SELECT CASE WHEN SUBSTRING('abcdefg', 1, 2) IN ('ab') THEN 1 ELSE 0 END",
+ "spark": "SELECT CASE WHEN SUBSTRING('abcdefg', 1, 2) IN ('ab') THEN 1 ELSE 0 END",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM x WHERE SUBSTRING(col1 FROM 3 + LENGTH(col1) - 10 FOR 10) IN (col2)",
+ write={
+ "hive": "SELECT * FROM x WHERE SUBSTRING(col1, 3 + LENGTH(col1) - 10, 10) IN (col2)",
+ "spark": "SELECT * FROM x WHERE SUBSTRING(col1, 3 + LENGTH(col1) - 10, 10) IN (col2)",
+ },
+ )
+ self.validate_all(
+ "SELECT SUBSTRING(CAST(2022 AS CHAR(4)) || LPAD(CAST(3 AS CHAR(2)), 2, '0') FROM 3 FOR 4)",
+ read={
+ "postgres": "SELECT SUBSTRING(2022::CHAR(4) || LPAD(3::CHAR(2), 2, '0') FROM 3 FOR 4)",
+ },
+ )
+ self.validate_all(
+ "SELECT TRIM(BOTH ' XXX ')",
+ write={
+ "mysql": "SELECT TRIM(' XXX ')",
+ "postgres": "SELECT TRIM(' XXX ')",
+ "hive": "SELECT TRIM(' XXX ')",
+ },
+ )
+ self.validate_all(
+ "TRIM(LEADING FROM ' XXX ')",
+ write={
+ "mysql": "LTRIM(' XXX ')",
+ "postgres": "LTRIM(' XXX ')",
+ "hive": "LTRIM(' XXX ')",
+ "presto": "LTRIM(' XXX ')",
+ },
+ )
+ self.validate_all(
+ "TRIM(TRAILING FROM ' XXX ')",
+ write={
+ "mysql": "RTRIM(' XXX ')",
+ "postgres": "RTRIM(' XXX ')",
+ "hive": "RTRIM(' XXX ')",
+ "presto": "RTRIM(' XXX ')",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) AS ss",
+ read={"postgres": "SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) AS ss"},
+ )
+ self.validate_all(
+ "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
+ read={
+ "postgres": "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
+ },
+ )
+ self.validate_all(
+ "SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
+ read={
+ "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons p1, polygons p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id",
+ },
+ )