Repository: tobymao/sqlglot

Issue:
Postgres array comparison operators not supported
Following Postgres array operations [1] raise ParseError:
```
SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2] AS left_contains;
SELECT ARRAY[1, 2, 3] && ARRAY[1, 2] AS has_overlap;
```

And this one is parsed but incorrectly (`<@` is parsed as `<`):
```
SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2] AS right_contains;
```

[1] https://www.postgresql.org/docs/current/functions-array.html

Discussion:

Patch:
diff --git a/sqlglot/dialects/drill.py b/sqlglot/dialects/drill.py
index afcf4d0e..0c1022d9 100644
--- a/sqlglot/dialects/drill.py
+++ b/sqlglot/dialects/drill.py
@@ -9,7 +9,6 @@ from sqlglot.dialects.dialect import (
     create_with_partitions_sql,
     datestrtodate_sql,
     format_time_lambda,
-    no_pivot_sql,
     no_trycast_sql,
     rename_func,
     str_position_sql,
@@ -136,7 +135,6 @@ class Drill(Dialect):
             exp.ILike: lambda self, e: f" {self.sql(e, 'this')} `ILIKE` {self.sql(e, 'expression')}",
             exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"),
             exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
-            exp.Pivot: no_pivot_sql,
             exp.RegexpLike: rename_func("REGEXP_MATCHES"),
             exp.StrPosition: str_position_sql,
             exp.StrToDate: _str_to_date,
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/postgres.py b/sqlglot/dialects/postgres.py
index 7e7902c7..b36ec9c2 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -15,6 +15,7 @@ from sqlglot.dialects.dialect import (
     trim_sql,
 )
 from sqlglot.helper import seq_get
+from sqlglot.parser import binary_range_parser
 from sqlglot.tokens import TokenType
 from sqlglot.transforms import delegate, preprocess
 
@@ -219,6 +220,8 @@ class Postgres(Dialect):
             "~~*": TokenType.ILIKE,
             "~*": TokenType.IRLIKE,
             "~": TokenType.RLIKE,
+            "@>": TokenType.AT_GT,
+            "<@": TokenType.LT_AT,
             "BEGIN": TokenType.COMMAND,
             "BEGIN TRANSACTION": TokenType.BEGIN,
             "BIGSERIAL": TokenType.BIGSERIAL,
@@ -260,7 +263,17 @@ class Postgres(Dialect):
             TokenType.HASH: exp.BitwiseXor,
         }
 
-        FACTOR = {**parser.Parser.FACTOR, TokenType.CARET: exp.Pow}
+        FACTOR = {
+            **parser.Parser.FACTOR,
+            TokenType.CARET: exp.Pow,
+        }
+
+        RANGE_PARSERS = {
+            **parser.Parser.RANGE_PARSERS,  # type: ignore
+            TokenType.DAMP: binary_range_parser(exp.ArrayOverlaps),
+            TokenType.AT_GT: binary_range_parser(exp.ArrayContains),
+            TokenType.LT_AT: binary_range_parser(exp.ArrayContained),
+        }
 
     class Generator(generator.Generator):
         LOCKING_READS_SUPPORTED = True
@@ -299,6 +312,9 @@ class Postgres(Dialect):
             exp.DateDiff: _date_diff_sql,
             exp.LogicalOr: rename_func("BOOL_OR"),
             exp.Min: min_or_least,
+            exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
+            exp.ArrayContains: lambda self, e: self.binary(e, "@>"),
+            exp.ArrayContained: lambda self, e: self.binary(e, "<@"),
             exp.RegexpLike: lambda self, e: self.binary(e, "~"),
             exp.RegexpILike: lambda self, e: self.binary(e, "~*"),
             exp.StrPosition: str_position_sql,
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 6413f6d2..73977c07 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -16,6 +16,7 @@ from sqlglot.dialects.dialect import (
 )
 from sqlglot.expressions import Literal
 from sqlglot.helper import flatten, seq_get
+from sqlglot.parser import binary_range_parser
 from sqlglot.tokens import TokenType
 
 
@@ -218,12 +219,8 @@ class Snowflake(Dialect):
 
         RANGE_PARSERS = {
             **parser.Parser.RANGE_PARSERS,  # type: ignore
-            TokenType.LIKE_ANY: lambda self, this: self._parse_escape(
-                self.expression(exp.LikeAny, this=this, expression=self._parse_bitwise())
-            ),
-            TokenType.ILIKE_ANY: lambda self, this: self._parse_escape(
-                self.expression(exp.ILikeAny, this=this, expression=self._parse_bitwise())
-            ),
+            TokenType.LIKE_ANY: binary_range_parser(exp.LikeAny),
+            TokenType.ILIKE_ANY: binary_range_parser(exp.ILikeAny),
         }
 
         ALTER_PARSERS = {
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..800ae4e2 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()
@@ -2971,6 +2976,10 @@ class Sub(Binary):
     pass
 
 
+class ArrayOverlaps(Binary):
+    pass
+
+
 # Unary Expressions
 # (NOT a)
 class Unary(Expression):
@@ -3156,8 +3165,12 @@ class ArrayConcat(Func):
     is_var_len_args = True
 
 
-class ArrayContains(Func):
-    arg_types = {"this": True, "expression": True}
+class ArrayContains(Binary, Func):
+    pass
+
+
+class ArrayContained(Binary):
+    pass
 
 
 class ArrayFilter(Func):
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 3ec51fb0..4eeaef81 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -31,6 +31,14 @@ def parse_var_map(args):
     )
 
 
+def binary_range_parser(
+    expr_type: t.Type[exp.Expression],
+) -> t.Callable[[Parser, t.Optional[exp.Expression]], t.Optional[exp.Expression]]:
+    return lambda self, this: self._parse_escape(
+        self.expression(expr_type, this=this, expression=self._parse_bitwise())
+    )
+
+
 class _Parser(type):
     def __new__(cls, clsname, bases, attrs):
         klass = super().__new__(cls, clsname, bases, attrs)
@@ -102,6 +110,7 @@ class Parser(metaclass=_Parser):
     }
 
     TYPE_TOKENS = {
+        TokenType.BIT,
         TokenType.BOOLEAN,
         TokenType.TINYINT,
         TokenType.SMALLINT,
@@ -503,29 +512,15 @@ class Parser(metaclass=_Parser):
 
     RANGE_PARSERS = {
         TokenType.BETWEEN: lambda self, this: self._parse_between(this),
-        TokenType.GLOB: lambda self, this: self._parse_escape(
-            self.expression(exp.Glob, this=this, expression=self._parse_bitwise())
-        ),
-        TokenType.OVERLAPS: lambda self, this: self._parse_escape(
-            self.expression(exp.Overlaps, this=this, expression=self._parse_bitwise())
-        ),
+        TokenType.GLOB: binary_range_parser(exp.Glob),
+        TokenType.OVERLAPS: binary_range_parser(exp.Overlaps),
         TokenType.IN: lambda self, this: self._parse_in(this),
         TokenType.IS: lambda self, this: self._parse_is(this),
-        TokenType.LIKE: lambda self, this: self._parse_escape(
-            self.expression(exp.Like, this=this, expression=self._parse_bitwise())
-        ),
-        TokenType.ILIKE: lambda self, this: self._parse_escape(
-            self.expression(exp.ILike, this=this, expression=self._parse_bitwise())
-        ),
-        TokenType.IRLIKE: lambda self, this: self.expression(
-            exp.RegexpILike, this=this, expression=self._parse_bitwise()
-        ),
-        TokenType.RLIKE: lambda self, this: self.expression(
-            exp.RegexpLike, this=this, expression=self._parse_bitwise()
-        ),
-        TokenType.SIMILAR_TO: lambda self, this: self.expression(
-            exp.SimilarTo, this=this, expression=self._parse_bitwise()
-        ),
+        TokenType.LIKE: binary_range_parser(exp.Like),
+        TokenType.ILIKE: binary_range_parser(exp.ILike),
+        TokenType.IRLIKE: binary_range_parser(exp.RegexpILike),
+        TokenType.RLIKE: binary_range_parser(exp.RegexpLike),
+        TokenType.SIMILAR_TO: binary_range_parser(exp.SimilarTo),
     }
 
     PROPERTY_PARSERS = {
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 5f4b77d2..053bbdd4 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -46,10 +46,13 @@ class TokenType(AutoName):
     HASH_ARROW = auto()
     DHASH_ARROW = auto()
     LR_ARROW = auto()
+    LT_AT = auto()
+    AT_GT = auto()
     DOLLAR = auto()
     PARAMETER = auto()
     SESSION_PARAMETER = auto()
     NATIONAL = auto()
+    DAMP = auto()
 
     BLOCK_START = auto()
     BLOCK_END = auto()
@@ -71,11 +74,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()
@@ -462,6 +470,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "#>": TokenType.HASH_ARROW,
         "#>>": TokenType.DHASH_ARROW,
         "<->": TokenType.LR_ARROW,
+        "&&": TokenType.DAMP,
         "ALL": TokenType.ALL,
         "ALWAYS": TokenType.ALWAYS,
         "AND": TokenType.AND,
@@ -630,6 +639,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_drill.py b/tests/dialects/test_drill.py
index a1960131..e41bd346 100644
--- a/tests/dialects/test_drill.py
+++ b/tests/dialects/test_drill.py
@@ -51,3 +51,16 @@ class TestDrill(Validator):
                 "drill": "SELECT * FROM table(dfs.`test_data.xlsx`(type => 'excel', sheetName => 'secondSheet'))",
             },
         )
+
+    def test_validate_pivot(self):
+        self.validate_all(
+            "SELECT * FROM (SELECT education_level, salary, marital_status, "
+            "EXTRACT(year FROM age(birth_date)) age FROM cp.`employee.json`) PIVOT (avg(salary) AS "
+            "avg_salary, avg(age) AS avg_age FOR marital_status IN ('M' married, 'S' single))",
+            write={
+                "drill": "SELECT * FROM (SELECT education_level, salary, marital_status, "
+                "EXTRACT(year FROM age(birth_date)) AS age FROM cp.`employee.json`) "
+                "PIVOT(AVG(salary) AS avg_salary, AVG(age) AS avg_age FOR marital_status "
+                "IN ('M' AS married, 'S' AS single))"
+            },
+        )
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..fa16732b 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -71,6 +71,9 @@ class TestPostgres(Validator):
             )
 
     def test_postgres(self):
+        self.validate_identity("SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]")
+        self.validate_identity("SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]")
+        self.validate_identity("SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]")
         self.validate_identity("$x")
         self.validate_identity("SELECT ARRAY[1, 2, 3]")
         self.validate_identity("SELECT ARRAY(SELECT 1)")
@@ -196,7 +199,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)",