Repository: tobymao/sqlglot

Issue:
sqlglot doesn't recognize postgres hstore or jsonb types
hstore example:
```
print(sqlglot.transpile("create table test(foo hstore)", read="postgres", pretty=True)[0])
```

```
sqlglot.errors.ParseError: Expecting ). Line 1, Col: 23.
  create table test(foo hstore)
```

jsonb example:
```
print(sqlglot.transpile("create table test(foo jsonb)", read="postgres", pretty=True)[0])
```

```
sqlglot.errors.ParseError: Expecting ). Line 1, Col: 23.
  create table test(foo jsonb)
```

Also I think array types aren't supported in some cases
```
print(sqlglot.transpile("create table test(foo varchar(64)[])", read="postgres", pretty=True)[0])
```

```
sqlglot.errors.ParseError: Expecting ). Line 1, Col: 34.
  create table test(foo varchar(64)[])
```

It does work if with simpler types like `foo int[]` etc but if the type has parens in it like this, it gives an error

Ideally it would recognize/handle these like other types - I know postgres has some non-standard types and it's something of a moving target, but of all the formtters I tried, sqlglot seems the nicest and it would be great if I could format all the create statements I have.

This is sqlglot==10.0.10 on python 3.10 if that matters





Discussion:

Patch:
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 4353164a..c4476d98 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -74,6 +74,12 @@ def _trim_sql(self, expression):
     return f"TRIM({trim_type}{remove_chars}{from_part}{target}{collation})"
 
 
+def _datatype_sql(self, expression):
+    if expression.this == exp.DataType.Type.ARRAY:
+        return f"{self.expressions(expression, flat=True)}[]"
+    return self.datatype_sql(expression)
+
+
 def _auto_increment_to_serial(expression):
     auto = expression.find(exp.AutoIncrementColumnConstraint)
 
@@ -191,25 +197,27 @@ class Postgres(Dialect):
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
             "ALWAYS": TokenType.ALWAYS,
-            "BY DEFAULT": TokenType.BY_DEFAULT,
-            "IDENTITY": TokenType.IDENTITY,
-            "GENERATED": TokenType.GENERATED,
-            "DOUBLE PRECISION": TokenType.DOUBLE,
-            "BIGSERIAL": TokenType.BIGSERIAL,
-            "SERIAL": TokenType.SERIAL,
-            "SMALLSERIAL": TokenType.SMALLSERIAL,
-            "UUID": TokenType.UUID,
-            "TEMP": TokenType.TEMPORARY,
-            "BEGIN TRANSACTION": TokenType.BEGIN,
             "BEGIN": TokenType.COMMAND,
+            "BEGIN TRANSACTION": TokenType.BEGIN,
+            "BIGSERIAL": TokenType.BIGSERIAL,
+            "BY DEFAULT": TokenType.BY_DEFAULT,
             "COMMENT ON": TokenType.COMMAND,
             "DECLARE": TokenType.COMMAND,
             "DO": TokenType.COMMAND,
+            "DOUBLE PRECISION": TokenType.DOUBLE,
+            "GENERATED": TokenType.GENERATED,
+            "GRANT": TokenType.COMMAND,
+            "HSTORE": TokenType.HSTORE,
+            "IDENTITY": TokenType.IDENTITY,
+            "JSONB": TokenType.JSONB,
             "REFRESH": TokenType.COMMAND,
             "REINDEX": TokenType.COMMAND,
             "RESET": TokenType.COMMAND,
             "REVOKE": TokenType.COMMAND,
-            "GRANT": TokenType.COMMAND,
+            "SERIAL": TokenType.SERIAL,
+            "SMALLSERIAL": TokenType.SMALLSERIAL,
+            "TEMP": TokenType.TEMPORARY,
+            "UUID": TokenType.UUID,
             **{f"CREATE {kind}": TokenType.COMMAND for kind in CREATABLES},
             **{f"DROP {kind}": TokenType.COMMAND for kind in CREATABLES},
         }
@@ -265,4 +273,5 @@ class Postgres(Dialect):
             exp.Trim: _trim_sql,
             exp.TryCast: no_trycast_sql,
             exp.UnixToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')})",
+            exp.DataType: _datatype_sql,
         }
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 357d375a..ae26db94 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2020,6 +2020,7 @@ class DataType(Expression):
         DECIMAL = auto()
         BOOLEAN = auto()
         JSON = auto()
+        JSONB = auto()
         INTERVAL = auto()
         TIMESTAMP = auto()
         TIMESTAMPTZ = auto()
@@ -2034,6 +2035,7 @@ class DataType(Expression):
         STRUCT = auto()
         NULLABLE = auto()
         HLLSKETCH = auto()
+        HSTORE = auto()
         SUPER = auto()
         SERIAL = auto()
         SMALLSERIAL = auto()
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 7830f62e..a543ad18 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -104,6 +104,7 @@ class Parser(metaclass=_Parser):
         TokenType.BINARY,
         TokenType.VARBINARY,
         TokenType.JSON,
+        TokenType.JSONB,
         TokenType.INTERVAL,
         TokenType.TIMESTAMP,
         TokenType.TIMESTAMPTZ,
@@ -115,6 +116,7 @@ class Parser(metaclass=_Parser):
         TokenType.GEOGRAPHY,
         TokenType.GEOMETRY,
         TokenType.HLLSKETCH,
+        TokenType.HSTORE,
         TokenType.SUPER,
         TokenType.SERIAL,
         TokenType.SMALLSERIAL,
@@ -1727,17 +1729,6 @@ class Parser(metaclass=_Parser):
         expressions = None
         maybe_func = False
 
-        if not nested and self._match_pair(TokenType.L_BRACKET, TokenType.R_BRACKET):
-            return exp.DataType(
-                this=exp.DataType.Type.ARRAY,
-                expressions=[exp.DataType.build(type_token.value)],
-                nested=True,
-            )
-
-        if self._match(TokenType.L_BRACKET):
-            self._retreat(index)
-            return None
-
         if self._match(TokenType.L_PAREN):
             if is_struct:
                 expressions = self._parse_csv(self._parse_struct_kwargs)
@@ -1753,6 +1744,17 @@ class Parser(metaclass=_Parser):
             self._match_r_paren()
             maybe_func = True
 
+        if not nested and self._match_pair(TokenType.L_BRACKET, TokenType.R_BRACKET):
+            return exp.DataType(
+                this=exp.DataType.Type.ARRAY,
+                expressions=[exp.DataType.build(type_token.value, expressions=expressions)],
+                nested=True,
+            )
+
+        if self._match(TokenType.L_BRACKET):
+            self._retreat(index)
+            return None
+
         if nested and self._match(TokenType.LT):
             if is_struct:
                 expressions = self._parse_csv(self._parse_struct_kwargs)
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 6d447dd7..fa006268 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -81,6 +81,7 @@ class TokenType(AutoName):
     BINARY = auto()
     VARBINARY = auto()
     JSON = auto()
+    JSONB = auto()
     TIMESTAMP = auto()
     TIMESTAMPTZ = auto()
     TIMESTAMPLTZ = auto()
@@ -91,6 +92,7 @@ class TokenType(AutoName):
     NULLABLE = auto()
     GEOMETRY = auto()
     HLLSKETCH = auto()
+    HSTORE = auto()
     SUPER = auto()
     SERIAL = auto()
     SMALLSERIAL = auto()


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 625156b5..06787022 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -89,6 +89,7 @@ class TestDuckDB(Validator):
                 "presto": "CAST(COL AS ARRAY(BIGINT))",
                 "hive": "CAST(COL AS ARRAY<BIGINT>)",
                 "spark": "CAST(COL AS ARRAY<LONG>)",
+                "postgres": "CAST(COL AS BIGINT[])",
             },
         )
 
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 8294eea7..c41fe01d 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -6,6 +6,9 @@ class TestPostgres(Validator):
     dialect = "postgres"
 
     def test_ddl(self):
+        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_all(
             "CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)",
             write={