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

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