sqlglot.dialects.redshift

 29class Redshift(Postgres):
 30    # https://docs.aws.amazon.com/redshift/latest/dg/r_names.html
 31    RESOLVES_IDENTIFIERS_AS_UPPERCASE = None
 32
 33    TIME_FORMAT = "'YYYY-MM-DD HH:MI:SS'"
 34    TIME_MAPPING = {
 35        **Postgres.TIME_MAPPING,
 36        "MON": "%b",
 37        "HH": "%H",
 38    }
 39
 40    class Parser(Postgres.Parser):
 41        FUNCTIONS = {
 42            **Postgres.Parser.FUNCTIONS,
 43            "ADD_MONTHS": lambda args: exp.DateAdd(
 44                this=exp.TsOrDsToDate(this=seq_get(args, 0)),
 45                expression=seq_get(args, 1),
 46                unit=exp.var("month"),
 47            ),
 48            "DATEADD": _parse_date_add,
 49            "DATE_ADD": _parse_date_add,
 50            "DATEDIFF": lambda args: exp.DateDiff(
 51                this=exp.TsOrDsToDate(this=seq_get(args, 2)),
 52                expression=exp.TsOrDsToDate(this=seq_get(args, 1)),
 53                unit=seq_get(args, 0),
 54            ),
 55            "STRTOL": exp.FromBase.from_arg_list,
 56        }
 57
 58        def _parse_types(
 59            self, check_func: bool = False, schema: bool = False
 60        ) -> t.Optional[exp.Expression]:
 61            this = super()._parse_types(check_func=check_func, schema=schema)
 62
 63            if (
 64                isinstance(this, exp.DataType)
 65                and this.is_type("varchar")
 66                and this.expressions
 67                and this.expressions[0].this == exp.column("MAX")
 68            ):
 69                this.set("expressions", [exp.var("MAX")])
 70
 71            return this
 72
 73        def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]:
 74            to = self._parse_types()
 75            self._match(TokenType.COMMA)
 76            this = self._parse_bitwise()
 77            return self.expression(exp.TryCast, this=this, to=to)
 78
 79    class Tokenizer(Postgres.Tokenizer):
 80        BIT_STRINGS = []
 81        HEX_STRINGS = []
 82        STRING_ESCAPES = ["\\"]
 83
 84        KEYWORDS = {
 85            **Postgres.Tokenizer.KEYWORDS,
 86            "HLLSKETCH": TokenType.HLLSKETCH,
 87            "SUPER": TokenType.SUPER,
 88            "SYSDATE": TokenType.CURRENT_TIMESTAMP,
 89            "TIME": TokenType.TIMESTAMP,
 90            "TIMETZ": TokenType.TIMESTAMPTZ,
 91            "TOP": TokenType.TOP,
 92            "UNLOAD": TokenType.COMMAND,
 93            "VARBYTE": TokenType.VARBINARY,
 94        }
 95
 96        # Redshift allows # to appear as a table identifier prefix
 97        SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy()
 98        SINGLE_TOKENS.pop("#")
 99
100    class Generator(Postgres.Generator):
101        LOCKING_READS_SUPPORTED = False
102        RENAME_TABLE_WITH_DB = False
103        QUERY_HINTS = False
104        VALUES_AS_TABLE = False
105
106        TYPE_MAPPING = {
107            **Postgres.Generator.TYPE_MAPPING,
108            exp.DataType.Type.BINARY: "VARBYTE",
109            exp.DataType.Type.VARBINARY: "VARBYTE",
110            exp.DataType.Type.INT: "INTEGER",
111        }
112
113        PROPERTIES_LOCATION = {
114            **Postgres.Generator.PROPERTIES_LOCATION,
115            exp.LikeProperty: exp.Properties.Location.POST_WITH,
116        }
117
118        TRANSFORMS = {
119            **Postgres.Generator.TRANSFORMS,
120            exp.Concat: concat_to_dpipe_sql,
121            exp.CurrentTimestamp: lambda self, e: "SYSDATE",
122            exp.DateAdd: lambda self, e: self.func(
123                "DATEADD", exp.var(e.text("unit") or "day"), e.expression, e.this
124            ),
125            exp.DateDiff: lambda self, e: self.func(
126                "DATEDIFF", exp.var(e.text("unit") or "day"), e.expression, e.this
127            ),
128            exp.DistKeyProperty: lambda self, e: f"DISTKEY({e.name})",
129            exp.DistStyleProperty: lambda self, e: self.naked_property(e),
130            exp.FromBase: rename_func("STRTOL"),
131            exp.JSONExtract: _json_sql,
132            exp.JSONExtractScalar: _json_sql,
133            exp.SafeConcat: concat_to_dpipe_sql,
134            exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]),
135            exp.SortKeyProperty: lambda self, e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})",
136            exp.TsOrDsToDate: ts_or_ds_to_date_sql("redshift"),
137        }
138
139        # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots
140        TRANSFORMS.pop(exp.Pivot)
141
142        # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres)
143        TRANSFORMS.pop(exp.Pow)
144
145        RESERVED_KEYWORDS = {*Postgres.Generator.RESERVED_KEYWORDS, "snapshot", "type"}
146
147        def with_properties(self, properties: exp.Properties) -> str:
148            """Redshift doesn't have `WITH` as part of their with_properties so we remove it"""
149            return self.properties(properties, prefix=" ", suffix="")
150
151        def datatype_sql(self, expression: exp.DataType) -> str:
152            """
153            Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
154            VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
155            without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
156            `TEXT` to `VARCHAR`.
157            """
158            if expression.is_type("text"):
159                expression = expression.copy()
160                expression.set("this", exp.DataType.Type.VARCHAR)
161                precision = expression.args.get("expressions")
162
163                if not precision:
164                    expression.append("expressions", exp.var("MAX"))
165
166            return super().datatype_sql(expression)

40    class Parser(Postgres.Parser):
41        FUNCTIONS = {
42            **Postgres.Parser.FUNCTIONS,
43            "ADD_MONTHS": lambda args: exp.DateAdd(
44                this=exp.TsOrDsToDate(this=seq_get(args, 0)),
45                expression=seq_get(args, 1),
46                unit=exp.var("month"),
47            ),
48            "DATEADD": _parse_date_add,
49            "DATE_ADD": _parse_date_add,
50            "DATEDIFF": lambda args: exp.DateDiff(
51                this=exp.TsOrDsToDate(this=seq_get(args, 2)),
52                expression=exp.TsOrDsToDate(this=seq_get(args, 1)),
53                unit=seq_get(args, 0),
54            ),
55            "STRTOL": exp.FromBase.from_arg_list,
56        }
57
58        def _parse_types(
59            self, check_func: bool = False, schema: bool = False
60        ) -> t.Optional[exp.Expression]:
61            this = super()._parse_types(check_func=check_func, schema=schema)
62
63            if (
64                isinstance(this, exp.DataType)
65                and this.is_type("varchar")
66                and this.expressions
67                and this.expressions[0].this == exp.column("MAX")
68            ):
69                this.set("expressions", [exp.var("MAX")])
70
71            return this
72
73        def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]:
74            to = self._parse_types()
75            self._match(TokenType.COMMA)
76            this = self._parse_bitwise()
77            return self.expression(exp.TryCast, this=this, to=to)

79    class Tokenizer(Postgres.Tokenizer):
80        BIT_STRINGS = []
81        HEX_STRINGS = []
82        STRING_ESCAPES = ["\\"]
83
84        KEYWORDS = {
85            **Postgres.Tokenizer.KEYWORDS,
86            "HLLSKETCH": TokenType.HLLSKETCH,
87            "SUPER": TokenType.SUPER,
88            "SYSDATE": TokenType.CURRENT_TIMESTAMP,
89            "TIME": TokenType.TIMESTAMP,
90            "TIMETZ": TokenType.TIMESTAMPTZ,
91            "TOP": TokenType.TOP,
92            "UNLOAD": TokenType.COMMAND,
93            "VARBYTE": TokenType.VARBINARY,
94        }
95
96        # Redshift allows # to appear as a table identifier prefix
97        SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy()
98        SINGLE_TOKENS.pop("#")

100    class Generator(Postgres.Generator):
101        LOCKING_READS_SUPPORTED = False
102        RENAME_TABLE_WITH_DB = False
103        QUERY_HINTS = False
104        VALUES_AS_TABLE = False
105
106        TYPE_MAPPING = {
107            **Postgres.Generator.TYPE_MAPPING,
108            exp.DataType.Type.BINARY: "VARBYTE",
109            exp.DataType.Type.VARBINARY: "VARBYTE",
110            exp.DataType.Type.INT: "INTEGER",
111        }
112
113        PROPERTIES_LOCATION = {
114            **Postgres.Generator.PROPERTIES_LOCATION,
115            exp.LikeProperty: exp.Properties.Location.POST_WITH,
116        }
117
118        TRANSFORMS = {
119            **Postgres.Generator.TRANSFORMS,
120            exp.Concat: concat_to_dpipe_sql,
121            exp.CurrentTimestamp: lambda self, e: "SYSDATE",
122            exp.DateAdd: lambda self, e: self.func(
123                "DATEADD", exp.var(e.text("unit") or "day"), e.expression, e.this
124            ),
125            exp.DateDiff: lambda self, e: self.func(
126                "DATEDIFF", exp.var(e.text("unit") or "day"), e.expression, e.this
127            ),
128            exp.DistKeyProperty: lambda self, e: f"DISTKEY({e.name})",
129            exp.DistStyleProperty: lambda self, e: self.naked_property(e),
130            exp.FromBase: rename_func("STRTOL"),
131            exp.JSONExtract: _json_sql,
132            exp.JSONExtractScalar: _json_sql,
133            exp.SafeConcat: concat_to_dpipe_sql,
134            exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]),
135            exp.SortKeyProperty: lambda self, e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})",
136            exp.TsOrDsToDate: ts_or_ds_to_date_sql("redshift"),
137        }
138
139        # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots
140        TRANSFORMS.pop(exp.Pivot)
141
142        # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres)
143        TRANSFORMS.pop(exp.Pow)
144
145        RESERVED_KEYWORDS = {*Postgres.Generator.RESERVED_KEYWORDS, "snapshot", "type"}
146
147        def with_properties(self, properties: exp.Properties) -> str:
148            """Redshift doesn't have `WITH` as part of their with_properties so we remove it"""
149            return self.properties(properties, prefix=" ", suffix="")
150
151        def datatype_sql(self, expression: exp.DataType) -> str:
152            """
153            Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
154            VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
155            without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
156            `TEXT` to `VARCHAR`.
157            """
158            if expression.is_type("text"):
159                expression = expression.copy()
160                expression.set("this", exp.DataType.Type.VARCHAR)
161                precision = expression.args.get("expressions")
162
163                if not precision:
164                    expression.append("expressions", exp.var("MAX"))
165
166            return super().datatype_sql(expression)