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

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