Repository: tobymao/sqlglot

Issue:
DuckDB unsigned integer types fail to parse
[Unsigned integer types](https://duckdb.org/docs/sql/data_types/overview.html#general-purpose-data-types) fail to parse:

```
In [13]: sg.parse_one("select cast(1 as UINTEGER)", read="duckdb")
...
ParseError: Expected TYPE after CAST. Line 1, Col: 18.
  select cast(1 as UINTEGER)
```

It would also be good to add support for all the type aliases if possible.

Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index db79d863..43f538cc 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -81,8 +81,21 @@ class DuckDB(Dialect):
             **tokens.Tokenizer.KEYWORDS,
             ":=": TokenType.EQ,
             "ATTACH": TokenType.COMMAND,
-            "CHARACTER VARYING": TokenType.VARCHAR,
+            "BINARY": TokenType.VARBINARY,
+            "BPCHAR": TokenType.TEXT,
+            "BITSTRING": TokenType.BIT,
+            "CHAR": TokenType.TEXT,
+            "CHARACTER VARYING": TokenType.TEXT,
             "EXCLUDE": TokenType.EXCEPT,
+            "INT1": TokenType.TINYINT,
+            "LOGICAL": TokenType.BOOLEAN,
+            "NUMERIC": TokenType.DOUBLE,
+            "SIGNED": TokenType.INT,
+            "STRING": TokenType.VARCHAR,
+            "UBIGINT": TokenType.UBIGINT,
+            "UINTEGER": TokenType.UINT,
+            "USMALLINT": TokenType.USMALLINT,
+            "UTINYINT": TokenType.UTINYINT,
         }
 
     class Parser(parser.Parser):
@@ -115,6 +128,14 @@ class DuckDB(Dialect):
             "UNNEST": exp.Explode.from_arg_list,
         }
 
+        TYPE_TOKENS = {
+            *parser.Parser.TYPE_TOKENS,
+            TokenType.UBIGINT,
+            TokenType.UINT,
+            TokenType.USMALLINT,
+            TokenType.UTINYINT,
+        }
+
     class Generator(generator.Generator):
         STRUCT_DELIMITER = ("(", ")")
 
@@ -169,8 +190,14 @@ class DuckDB(Dialect):
 
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,  # type: ignore
-            exp.DataType.Type.VARCHAR: "TEXT",
+            exp.DataType.Type.BINARY: "BLOB",
+            exp.DataType.Type.CHAR: "TEXT",
+            exp.DataType.Type.FLOAT: "REAL",
+            exp.DataType.Type.NCHAR: "TEXT",
             exp.DataType.Type.NVARCHAR: "TEXT",
+            exp.DataType.Type.UINT: "UINTEGER",
+            exp.DataType.Type.VARBINARY: "BLOB",
+            exp.DataType.Type.VARCHAR: "TEXT",
         }
 
         STAR_MAPPING = {
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index d07b0833..4d0be316 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -248,7 +248,6 @@ class TSQL(Dialect):
 
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
-            "BIT": TokenType.BOOLEAN,
             "DATETIME2": TokenType.DATETIME,
             "DATETIMEOFFSET": TokenType.TIMESTAMPTZ,
             "DECLARE": TokenType.COMMAND,
@@ -421,7 +420,6 @@ class TSQL(Dialect):
 
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,  # type: ignore
-            exp.DataType.Type.BOOLEAN: "BIT",
             exp.DataType.Type.INT: "INTEGER",
             exp.DataType.Type.DECIMAL: "NUMERIC",
             exp.DataType.Type.DATETIME: "DATETIME2",
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 5caf210a..9d51800b 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2656,12 +2656,17 @@ class DataType(Expression):
         BINARY = auto()
         VARBINARY = auto()
         INT = auto()
+        UINT = auto()
         TINYINT = auto()
+        UTINYINT = auto()
         SMALLINT = auto()
+        USMALLINT = auto()
         BIGINT = auto()
+        UBIGINT = auto()
         FLOAT = auto()
         DOUBLE = auto()
         DECIMAL = auto()
+        BIT = auto()
         BOOLEAN = auto()
         JSON = auto()
         JSONB = auto()
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 3ec51fb0..3bc31708 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -102,6 +102,7 @@ class Parser(metaclass=_Parser):
     }
 
     TYPE_TOKENS = {
+        TokenType.BIT,
         TokenType.BOOLEAN,
         TokenType.TINYINT,
         TokenType.SMALLINT,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 5f4b77d2..3adeeb7d 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -71,11 +71,16 @@ class TokenType(AutoName):
     BYTE_STRING = auto()
 
     # types
+    BIT = auto()
     BOOLEAN = auto()
     TINYINT = auto()
+    UTINYINT = auto()
     SMALLINT = auto()
+    USMALLINT = auto()
     INT = auto()
+    UINT = auto()
     BIGINT = auto()
+    UBIGINT = auto()
     FLOAT = auto()
     DOUBLE = auto()
     DECIMAL = auto()
@@ -630,6 +635,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "WITHOUT TIME ZONE": TokenType.WITHOUT_TIME_ZONE,
         "APPLY": TokenType.APPLY,
         "ARRAY": TokenType.ARRAY,
+        "BIT": TokenType.BIT,
         "BOOL": TokenType.BOOLEAN,
         "BOOLEAN": TokenType.BOOLEAN,
         "BYTE": TokenType.TINYINT,


Test patch:
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 69563cb4..21f6be6e 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -98,7 +98,7 @@ class TestDialect(Validator):
                 "bigquery": "CAST(a AS BINARY)",
                 "clickhouse": "CAST(a AS BINARY(4))",
                 "drill": "CAST(a AS VARBINARY(4))",
-                "duckdb": "CAST(a AS BINARY(4))",
+                "duckdb": "CAST(a AS BLOB(4))",
                 "mysql": "CAST(a AS BINARY(4))",
                 "hive": "CAST(a AS BINARY(4))",
                 "oracle": "CAST(a AS BLOB(4))",
@@ -116,7 +116,7 @@ class TestDialect(Validator):
             write={
                 "bigquery": "CAST(a AS VARBINARY)",
                 "clickhouse": "CAST(a AS VARBINARY(4))",
-                "duckdb": "CAST(a AS VARBINARY(4))",
+                "duckdb": "CAST(a AS BLOB(4))",
                 "mysql": "CAST(a AS VARBINARY(4))",
                 "hive": "CAST(a AS BINARY(4))",
                 "oracle": "CAST(a AS BLOB(4))",
@@ -1208,7 +1208,7 @@ class TestDialect(Validator):
         self.validate_all(
             "CREATE TABLE t (c CHAR, nc NCHAR, v1 VARCHAR, v2 VARCHAR2, nv NVARCHAR, nv2 NVARCHAR2)",
             write={
-                "duckdb": "CREATE TABLE t (c CHAR, nc CHAR, v1 TEXT, v2 TEXT, nv TEXT, nv2 TEXT)",
+                "duckdb": "CREATE TABLE t (c TEXT, nc TEXT, v1 TEXT, v2 TEXT, nv TEXT, nv2 TEXT)",
                 "hive": "CREATE TABLE t (c CHAR, nc CHAR, v1 STRING, v2 STRING, nv STRING, nv2 STRING)",
                 "oracle": "CREATE TABLE t (c CHAR, nc CHAR, v1 VARCHAR2, v2 VARCHAR2, nv NVARCHAR2, nv2 NVARCHAR2)",
                 "postgres": "CREATE TABLE t (c CHAR, nc CHAR, v1 VARCHAR, v2 VARCHAR, nv VARCHAR, nv2 VARCHAR)",
@@ -1249,7 +1249,7 @@ class TestDialect(Validator):
         self.validate_all(
             "CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 TEXT, c2 TEXT(1024))",
             write={
-                "duckdb": "CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 TEXT, c2 TEXT(1024))",
+                "duckdb": "CREATE TABLE t (b1 BLOB, b2 BLOB(1024), c1 TEXT, c2 TEXT(1024))",
                 "hive": "CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 STRING, c2 STRING(1024))",
                 "oracle": "CREATE TABLE t (b1 BLOB, b2 BLOB(1024), c1 CLOB, c2 CLOB(1024))",
                 "postgres": "CREATE TABLE t (b1 BYTEA, b2 BYTEA(1024), c1 TEXT, c2 TEXT(1024))",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a1a0090d..4e975128 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -120,28 +120,17 @@ class TestDuckDB(Validator):
         self.validate_identity("SELECT ROW(x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
         self.validate_identity("SELECT (x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)")
         self.validate_identity("SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a)")
+        self.validate_identity("ATTACH DATABASE ':memory:' AS new_database")
         self.validate_identity(
             "SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a)"
         )
+
         self.validate_all(
             "CREATE TABLE IF NOT EXISTS table (cola INT, colb STRING) USING ICEBERG PARTITIONED BY (colb)",
             write={
                 "duckdb": "CREATE TABLE IF NOT EXISTS table (cola INT, colb TEXT)",
             },
         )
-
-        self.validate_all(
-            "COL::BIGINT[]",
-            write={
-                "duckdb": "CAST(COL AS BIGINT[])",
-                "presto": "CAST(COL AS ARRAY(BIGINT))",
-                "hive": "CAST(COL AS ARRAY<BIGINT>)",
-                "spark": "CAST(COL AS ARRAY<LONG>)",
-                "postgres": "CAST(COL AS BIGINT[])",
-                "snowflake": "CAST(COL AS ARRAY)",
-            },
-        )
-
         self.validate_all(
             "LIST_VALUE(0, 1, 2)",
             read={
@@ -212,7 +201,6 @@ class TestDuckDB(Validator):
                 "spark": "x.`y`.`abc`",
             },
         )
-
         self.validate_all(
             "QUANTILE(x, 0.5)",
             write={
@@ -222,14 +210,6 @@ class TestDuckDB(Validator):
                 "spark": "PERCENTILE(x, 0.5)",
             },
         )
-
-        self.validate_all(
-            "CAST(x AS DATE)",
-            write={
-                "duckdb": "CAST(x AS DATE)",
-                "": "CAST(x AS DATE)",
-            },
-        )
         self.validate_all(
             "UNNEST(x)",
             read={
@@ -240,7 +220,6 @@ class TestDuckDB(Validator):
                 "spark": "EXPLODE(x)",
             },
         )
-
         self.validate_all(
             "1d",
             write={
@@ -248,13 +227,6 @@ class TestDuckDB(Validator):
                 "spark": "1 AS d",
             },
         )
-        self.validate_all(
-            "CAST(1 AS DOUBLE)",
-            read={
-                "hive": "1d",
-                "spark": "1d",
-            },
-        )
         self.validate_all(
             "POWER(CAST(2 AS SMALLINT), 3)",
             read={
@@ -351,8 +323,6 @@ class TestDuckDB(Validator):
             },
         )
 
-        self.validate_identity("ATTACH DATABASE ':memory:' AS new_database")
-
         with self.assertRaises(UnsupportedError):
             transpile(
                 "SELECT a FROM b PIVOT(SUM(x) FOR y IN ('z', 'q'))",
@@ -372,13 +342,44 @@ class TestDuckDB(Validator):
         self.validate_identity("ARRAY(SELECT id FROM t)")
 
     def test_cast(self):
+        self.validate_identity("CAST(x AS REAL)")
+        self.validate_identity("CAST(x AS UINTEGER)")
+        self.validate_identity("CAST(x AS UBIGINT)")
+        self.validate_identity("CAST(x AS USMALLINT)")
+        self.validate_identity("CAST(x AS UTINYINT)")
+        self.validate_identity("CAST(x AS TEXT)")
+
+        self.validate_all("CAST(x AS NUMERIC)", write={"duckdb": "CAST(x AS DOUBLE)"})
+        self.validate_all("CAST(x AS CHAR)", write={"duckdb": "CAST(x AS TEXT)"})
+        self.validate_all("CAST(x AS BPCHAR)", write={"duckdb": "CAST(x AS TEXT)"})
+        self.validate_all("CAST(x AS STRING)", write={"duckdb": "CAST(x AS TEXT)"})
+        self.validate_all("CAST(x AS INT1)", write={"duckdb": "CAST(x AS TINYINT)"})
+        self.validate_all("CAST(x AS FLOAT4)", write={"duckdb": "CAST(x AS REAL)"})
+        self.validate_all("CAST(x AS FLOAT)", write={"duckdb": "CAST(x AS REAL)"})
+        self.validate_all("CAST(x AS INT4)", write={"duckdb": "CAST(x AS INT)"})
+        self.validate_all("CAST(x AS INTEGER)", write={"duckdb": "CAST(x AS INT)"})
+        self.validate_all("CAST(x AS SIGNED)", write={"duckdb": "CAST(x AS INT)"})
+        self.validate_all("CAST(x AS BLOB)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS BYTEA)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS BINARY)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS VARBINARY)", write={"duckdb": "CAST(x AS BLOB)"})
+        self.validate_all("CAST(x AS LOGICAL)", write={"duckdb": "CAST(x AS BOOLEAN)"})
+        self.validate_all(
+            "CAST(x AS BIT)",
+            read={
+                "duckdb": "CAST(x AS BITSTRING)",
+            },
+            write={
+                "duckdb": "CAST(x AS BIT)",
+                "tsql": "CAST(x AS BIT)",
+            },
+        )
         self.validate_all(
             "123::CHARACTER VARYING",
             write={
                 "duckdb": "CAST(123 AS TEXT)",
             },
         )
-
         self.validate_all(
             "cast([[1]] as int[][])",
             write={
@@ -386,10 +387,34 @@ class TestDuckDB(Validator):
                 "spark": "CAST(ARRAY(ARRAY(1)) AS ARRAY<ARRAY<INT>>)",
             },
         )
-
         self.validate_all(
             "CAST(x AS DATE) + INTERVAL (7 * -1) DAY", read={"spark": "DATE_SUB(x, 7)"}
         )
+        self.validate_all(
+            "CAST(1 AS DOUBLE)",
+            read={
+                "hive": "1d",
+                "spark": "1d",
+            },
+        )
+        self.validate_all(
+            "CAST(x AS DATE)",
+            write={
+                "duckdb": "CAST(x AS DATE)",
+                "": "CAST(x AS DATE)",
+            },
+        )
+        self.validate_all(
+            "COL::BIGINT[]",
+            write={
+                "duckdb": "CAST(COL AS BIGINT[])",
+                "presto": "CAST(COL AS ARRAY(BIGINT))",
+                "hive": "CAST(COL AS ARRAY<BIGINT>)",
+                "spark": "CAST(COL AS ARRAY<LONG>)",
+                "postgres": "CAST(COL AS BIGINT[])",
+                "snowflake": "CAST(COL AS ARRAY)",
+            },
+        )
 
     def test_bool_or(self):
         self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 0881a89d..aebd277a 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -196,7 +196,7 @@ class TestPostgres(Validator):
         self.validate_all(
             "CREATE TABLE x (a UUID, b BYTEA)",
             write={
-                "duckdb": "CREATE TABLE x (a UUID, b VARBINARY)",
+                "duckdb": "CREATE TABLE x (a UUID, b BLOB)",
                 "presto": "CREATE TABLE x (a UUID, b VARBINARY)",
                 "hive": "CREATE TABLE x (a UUID, b BINARY)",
                 "spark": "CREATE TABLE x (a UUID, b BINARY)",