sqlglot.dialects.duckdb

 18class DuckDB(Dialect):
 19    NULL_ORDERING = "nulls_are_last"
 20    SUPPORTS_USER_DEFINED_TYPES = True
 21    INDEX_OFFSET = 1
 22    CONCAT_COALESCE = True
 23    CONCAT_WS_COALESCE = True
 24    SUPPORTS_ORDER_BY_ALL = True
 25    SUPPORTS_LIMIT_ALL = True
 26    SUPPORTS_FIXED_SIZE_ARRAYS = True
 27    TABLES_REFERENCEABLE_AS_COLUMNS = True
 28    STRICT_JSON_PATH_SYNTAX = False
 29    NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = True
 30    UUID_IS_STRING_TYPE = False
 31    USING_COLUMN_ORDER = UsingColumnOrder.IN_PLACE
 32
 33    # https://duckdb.org/docs/sql/introduction.html#creating-a-new-table
 34    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
 35    ASCII_ONLY_NORMALIZATION = True
 36
 37    DATE_PART_MAPPING = {
 38        **Dialect.DATE_PART_MAPPING,
 39        "DAYOFWEEKISO": "ISODOW",
 40    }
 41
 42    EXPRESSION_METADATA = EXPRESSION_METADATA.copy()
 43
 44    DATE_PART_MAPPING.pop("WEEKDAY")
 45
 46    INVERSE_TIME_MAPPING = {
 47        "%e": "%-d",  # BigQuery's space-padded day (%e) -> DuckDB's no-padding day (%-d)
 48        "%:z": "%z",  # In DuckDB %z	can represent +/-HH:MM, +/-HHMM, or +/-HH.
 49        "%-z": "%z",
 50        "%f_zero": "%n",
 51        "%f_one": "%n",
 52        "%f_two": "%n",
 53        "%f_three": "%g",
 54        "%f_four": "%n",
 55        "%f_five": "%n",
 56        "%f_seven": "%n",
 57        "%f_eight": "%n",
 58        "%f_nine": "%n",
 59    }
 60
 61    def to_json_path(self, path: exp.Expr | None) -> exp.Expr | None:
 62        if isinstance(path, exp.Literal):
 63            # DuckDB also supports the JSON pointer syntax, where every path starts with a `/`.
 64            # Additionally, it allows accessing the back of lists using the `[#-i]` syntax.
 65            # This check ensures we'll avoid trying to parse these as JSON paths, which can
 66            # either result in a noisy warning or in an invalid representation of the path.
 67            path_text = path.name
 68            if path_text.startswith("/") or "[#" in path_text:
 69                return path
 70
 71        return super().to_json_path(path)
 72
 73    UNESCAPED_SEQUENCES = {"\\a": "a", "\\v": "v"}
 74
 75    class Tokenizer(tokens.Tokenizer):
 76        NUMERIC_ESCAPES = {"x": (16, 1, 2, 0xFF), "0": (8, 1, 3, 0o777)}
 77        NUMERIC_ESCAPES_ARE_BYTES = True
 78        DROP_UNKNOWN_ESCAPES = True
 79        BYTE_STRINGS = [("e'", "'"), ("E'", "'")]
 80        BYTE_STRING_ESCAPES = ["'", "\\"]
 81        HEREDOC_STRINGS = ["$"]
 82
 83        HEREDOC_TAG_IS_IDENTIFIER = True
 84        HEREDOC_STRING_ALTERNATIVE = TokenType.PARAMETER
 85
 86        KEYWORDS = {
 87            **tokens.Tokenizer.KEYWORDS,
 88            "//": TokenType.DIV,
 89            "**": TokenType.DSTAR,
 90            "^@": TokenType.CARET_AT,
 91            "@>": TokenType.AT_GT,
 92            "<@": TokenType.LT_AT,
 93            "ATTACH": TokenType.ATTACH,
 94            "BINARY": TokenType.VARBINARY,
 95            "BITSTRING": TokenType.BIT,
 96            "BPCHAR": TokenType.TEXT,
 97            "CHAR": TokenType.TEXT,
 98            "DATETIME": TokenType.TIMESTAMPNTZ,
 99            "DETACH": TokenType.DETACH,
100            "FORCE": TokenType.FORCE,
101            "INSTALL": TokenType.INSTALL,
102            "INT8": TokenType.BIGINT,
103            "LOGICAL": TokenType.BOOLEAN,
104            "MACRO": TokenType.FUNCTION,
105            "ONLY": TokenType.ONLY,
106            "PIVOT_WIDER": TokenType.PIVOT,
107            "POSITIONAL": TokenType.POSITIONAL,
108            "RESET": TokenType.COMMAND,
109            "ROW": TokenType.STRUCT,
110            "SIGNED": TokenType.INT,
111            "STRING": TokenType.TEXT,
112            "SUMMARIZE": TokenType.SUMMARIZE,
113            "TIMESTAMP": TokenType.TIMESTAMPNTZ,
114            "TIMESTAMP_S": TokenType.TIMESTAMP_S,
115            "TIMESTAMP_MS": TokenType.TIMESTAMP_MS,
116            "TIMESTAMP_NS": TokenType.TIMESTAMP_NS,
117            "TIMESTAMP_US": TokenType.TIMESTAMP,
118            "UBIGINT": TokenType.UBIGINT,
119            "UINTEGER": TokenType.UINT,
120            "USMALLINT": TokenType.USMALLINT,
121            "UTINYINT": TokenType.UTINYINT,
122            "VARCHAR": TokenType.TEXT,
123        }
124        KEYWORDS.pop("/*+")
125
126        SINGLE_TOKENS = {
127            **tokens.Tokenizer.SINGLE_TOKENS,
128            "$": TokenType.PARAMETER,
129        }
130
131        VAR_SINGLE_TOKENS = {"$"}
132
133        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
134
135    Parser = DuckDBParser
136
137    Generator = DuckDBGenerator

 75    class Tokenizer(tokens.Tokenizer):
 76        NUMERIC_ESCAPES = {"x": (16, 1, 2, 0xFF), "0": (8, 1, 3, 0o777)}
 77        NUMERIC_ESCAPES_ARE_BYTES = True
 78        DROP_UNKNOWN_ESCAPES = True
 79        BYTE_STRINGS = [("e'", "'"), ("E'", "'")]
 80        BYTE_STRING_ESCAPES = ["'", "\\"]
 81        HEREDOC_STRINGS = ["$"]
 82
 83        HEREDOC_TAG_IS_IDENTIFIER = True
 84        HEREDOC_STRING_ALTERNATIVE = TokenType.PARAMETER
 85
 86        KEYWORDS = {
 87            **tokens.Tokenizer.KEYWORDS,
 88            "//": TokenType.DIV,
 89            "**": TokenType.DSTAR,
 90            "^@": TokenType.CARET_AT,
 91            "@>": TokenType.AT_GT,
 92            "<@": TokenType.LT_AT,
 93            "ATTACH": TokenType.ATTACH,
 94            "BINARY": TokenType.VARBINARY,
 95            "BITSTRING": TokenType.BIT,
 96            "BPCHAR": TokenType.TEXT,
 97            "CHAR": TokenType.TEXT,
 98            "DATETIME": TokenType.TIMESTAMPNTZ,
 99            "DETACH": TokenType.DETACH,
100            "FORCE": TokenType.FORCE,
101            "INSTALL": TokenType.INSTALL,
102            "INT8": TokenType.BIGINT,
103            "LOGICAL": TokenType.BOOLEAN,
104            "MACRO": TokenType.FUNCTION,
105            "ONLY": TokenType.ONLY,
106            "PIVOT_WIDER": TokenType.PIVOT,
107            "POSITIONAL": TokenType.POSITIONAL,
108            "RESET": TokenType.COMMAND,
109            "ROW": TokenType.STRUCT,
110            "SIGNED": TokenType.INT,
111            "STRING": TokenType.TEXT,
112            "SUMMARIZE": TokenType.SUMMARIZE,
113            "TIMESTAMP": TokenType.TIMESTAMPNTZ,
114            "TIMESTAMP_S": TokenType.TIMESTAMP_S,
115            "TIMESTAMP_MS": TokenType.TIMESTAMP_MS,
116            "TIMESTAMP_NS": TokenType.TIMESTAMP_NS,
117            "TIMESTAMP_US": TokenType.TIMESTAMP,
118            "UBIGINT": TokenType.UBIGINT,
119            "UINTEGER": TokenType.UINT,
120            "USMALLINT": TokenType.USMALLINT,
121            "UTINYINT": TokenType.UTINYINT,
122            "VARCHAR": TokenType.TEXT,
123        }
124        KEYWORDS.pop("/*+")
125
126        SINGLE_TOKENS = {
127            **tokens.Tokenizer.SINGLE_TOKENS,
128            "$": TokenType.PARAMETER,
129        }
130
131        VAR_SINGLE_TOKENS = {"$"}
132
133        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}