sqlglot.dialects.duckdb

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

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