sqlglot.dialects.redshift

 36class Redshift(Postgres):
 37    # https://docs.aws.amazon.com/redshift/latest/dg/r_names.html
 38    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
 39
 40    SUPPORTS_USER_DEFINED_TYPES = False
 41    INDEX_OFFSET = 0
 42
 43    TIME_FORMAT = "'YYYY-MM-DD HH:MI:SS'"
 44    TIME_MAPPING = {
 45        **Postgres.TIME_MAPPING,
 46        "MON": "%b",
 47        "HH": "%H",
 48    }
 49
 50    class Parser(Postgres.Parser):
 51        FUNCTIONS = {
 52            **Postgres.Parser.FUNCTIONS,
 53            "ADD_MONTHS": lambda args: exp.TsOrDsAdd(
 54                this=seq_get(args, 0),
 55                expression=seq_get(args, 1),
 56                unit=exp.var("month"),
 57                return_type=exp.DataType.build("TIMESTAMP"),
 58            ),
 59            "DATEADD": _parse_date_delta(exp.TsOrDsAdd),
 60            "DATE_ADD": _parse_date_delta(exp.TsOrDsAdd),
 61            "DATEDIFF": _parse_date_delta(exp.TsOrDsDiff),
 62            "DATE_DIFF": _parse_date_delta(exp.TsOrDsDiff),
 63            "GETDATE": exp.CurrentTimestamp.from_arg_list,
 64            "LISTAGG": exp.GroupConcat.from_arg_list,
 65            "STRTOL": exp.FromBase.from_arg_list,
 66        }
 67
 68        NO_PAREN_FUNCTION_PARSERS = {
 69            **Postgres.Parser.NO_PAREN_FUNCTION_PARSERS,
 70            "APPROXIMATE": lambda self: self._parse_approximate_count(),
 71            "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, transaction=True),
 72        }
 73
 74        def _parse_table(
 75            self,
 76            schema: bool = False,
 77            joins: bool = False,
 78            alias_tokens: t.Optional[t.Collection[TokenType]] = None,
 79            parse_bracket: bool = False,
 80            is_db_reference: bool = False,
 81        ) -> t.Optional[exp.Expression]:
 82            # Redshift supports UNPIVOTing SUPER objects, e.g. `UNPIVOT foo.obj[0] AS val AT attr`
 83            unpivot = self._match(TokenType.UNPIVOT)
 84            table = super()._parse_table(
 85                schema=schema,
 86                joins=joins,
 87                alias_tokens=alias_tokens,
 88                parse_bracket=parse_bracket,
 89                is_db_reference=is_db_reference,
 90            )
 91
 92            return self.expression(exp.Pivot, this=table, unpivot=True) if unpivot else table
 93
 94        def _parse_types(
 95            self, check_func: bool = False, schema: bool = False, allow_identifiers: bool = True
 96        ) -> t.Optional[exp.Expression]:
 97            this = super()._parse_types(
 98                check_func=check_func, schema=schema, allow_identifiers=allow_identifiers
 99            )
100
101            if (
102                isinstance(this, exp.DataType)
103                and this.is_type("varchar")
104                and this.expressions
105                and this.expressions[0].this == exp.column("MAX")
106            ):
107                this.set("expressions", [exp.var("MAX")])
108
109            return this
110
111        def _parse_convert(
112            self, strict: bool, safe: t.Optional[bool] = None
113        ) -> t.Optional[exp.Expression]:
114            to = self._parse_types()
115            self._match(TokenType.COMMA)
116            this = self._parse_bitwise()
117            return self.expression(exp.TryCast, this=this, to=to, safe=safe)
118
119        def _parse_approximate_count(self) -> t.Optional[exp.ApproxDistinct]:
120            index = self._index - 1
121            func = self._parse_function()
122
123            if isinstance(func, exp.Count) and isinstance(func.this, exp.Distinct):
124                return self.expression(exp.ApproxDistinct, this=seq_get(func.this.expressions, 0))
125            self._retreat(index)
126            return None
127
128        def _parse_query_modifiers(
129            self, this: t.Optional[exp.Expression]
130        ) -> t.Optional[exp.Expression]:
131            this = super()._parse_query_modifiers(this)
132
133            if this:
134                refs = set()
135
136                for i, join in enumerate(this.args.get("joins", [])):
137                    refs.add(
138                        (
139                            this.args["from"] if i == 0 else this.args["joins"][i - 1]
140                        ).this.alias.lower()
141                    )
142
143                    table = join.this
144                    if isinstance(table, exp.Table) and not join.args.get("on"):
145                        if table.parts[0].name.lower() in refs:
146                            table.replace(table.to_column())
147            return this
148
149    class Tokenizer(Postgres.Tokenizer):
150        BIT_STRINGS = []
151        HEX_STRINGS = []
152        STRING_ESCAPES = ["\\", "'"]
153
154        KEYWORDS = {
155            **Postgres.Tokenizer.KEYWORDS,
156            "HLLSKETCH": TokenType.HLLSKETCH,
157            "SUPER": TokenType.SUPER,
158            "TOP": TokenType.TOP,
159            "UNLOAD": TokenType.COMMAND,
160            "VARBYTE": TokenType.VARBINARY,
161        }
162
163        # Redshift allows # to appear as a table identifier prefix
164        SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy()
165        SINGLE_TOKENS.pop("#")
166
167    class Generator(Postgres.Generator):
168        LOCKING_READS_SUPPORTED = False
169        QUERY_HINTS = False
170        VALUES_AS_TABLE = False
171        TZ_TO_WITH_TIME_ZONE = True
172        NVL2_SUPPORTED = True
173        LAST_DAY_SUPPORTS_DATE_PART = False
174
175        TYPE_MAPPING = {
176            **Postgres.Generator.TYPE_MAPPING,
177            exp.DataType.Type.BINARY: "VARBYTE",
178            exp.DataType.Type.INT: "INTEGER",
179            exp.DataType.Type.TIMETZ: "TIME",
180            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
181            exp.DataType.Type.VARBINARY: "VARBYTE",
182        }
183
184        TRANSFORMS = {
185            **Postgres.Generator.TRANSFORMS,
186            exp.Concat: concat_to_dpipe_sql,
187            exp.ConcatWs: concat_ws_to_dpipe_sql,
188            exp.ApproxDistinct: lambda self,
189            e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})",
190            exp.CurrentTimestamp: lambda self, e: (
191                "SYSDATE" if e.args.get("transaction") else "GETDATE()"
192            ),
193            exp.DateAdd: date_delta_sql("DATEADD"),
194            exp.DateDiff: date_delta_sql("DATEDIFF"),
195            exp.DistKeyProperty: lambda self, e: f"DISTKEY({e.name})",
196            exp.DistStyleProperty: lambda self, e: self.naked_property(e),
197            exp.FromBase: rename_func("STRTOL"),
198            exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql,
199            exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
200            exp.GroupConcat: rename_func("LISTAGG"),
201            exp.ParseJSON: rename_func("JSON_PARSE"),
202            exp.Select: transforms.preprocess(
203                [transforms.eliminate_distinct_on, transforms.eliminate_semi_and_anti_joins]
204            ),
205            exp.SortKeyProperty: lambda self,
206            e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})",
207            exp.TableSample: no_tablesample_sql,
208            exp.TsOrDsAdd: date_delta_sql("DATEADD"),
209            exp.TsOrDsDiff: date_delta_sql("DATEDIFF"),
210        }
211
212        # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots
213        TRANSFORMS.pop(exp.Pivot)
214
215        # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres)
216        TRANSFORMS.pop(exp.Pow)
217
218        # Redshift supports ANY_VALUE(..)
219        TRANSFORMS.pop(exp.AnyValue)
220
221        # Redshift supports LAST_DAY(..)
222        TRANSFORMS.pop(exp.LastDay)
223
224        RESERVED_KEYWORDS = {*Postgres.Generator.RESERVED_KEYWORDS, "snapshot", "type"}
225
226        def with_properties(self, properties: exp.Properties) -> str:
227            """Redshift doesn't have `WITH` as part of their with_properties so we remove it"""
228            return self.properties(properties, prefix=" ", suffix="")
229
230        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
231            if expression.is_type(exp.DataType.Type.JSON):
232                # Redshift doesn't support a JSON type, so casting to it is treated as a noop
233                return self.sql(expression, "this")
234
235            return super().cast_sql(expression, safe_prefix=safe_prefix)
236
237        def datatype_sql(self, expression: exp.DataType) -> str:
238            """
239            Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
240            VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
241            without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
242            `TEXT` to `VARCHAR`.
243            """
244            if expression.is_type("text"):
245                expression.set("this", exp.DataType.Type.VARCHAR)
246                precision = expression.args.get("expressions")
247
248                if not precision:
249                    expression.append("expressions", exp.var("MAX"))
250
251            return super().datatype_sql(expression)

 50    class Parser(Postgres.Parser):
 51        FUNCTIONS = {
 52            **Postgres.Parser.FUNCTIONS,
 53            "ADD_MONTHS": lambda args: exp.TsOrDsAdd(
 54                this=seq_get(args, 0),
 55                expression=seq_get(args, 1),
 56                unit=exp.var("month"),
 57                return_type=exp.DataType.build("TIMESTAMP"),
 58            ),
 59            "DATEADD": _parse_date_delta(exp.TsOrDsAdd),
 60            "DATE_ADD": _parse_date_delta(exp.TsOrDsAdd),
 61            "DATEDIFF": _parse_date_delta(exp.TsOrDsDiff),
 62            "DATE_DIFF": _parse_date_delta(exp.TsOrDsDiff),
 63            "GETDATE": exp.CurrentTimestamp.from_arg_list,
 64            "LISTAGG": exp.GroupConcat.from_arg_list,
 65            "STRTOL": exp.FromBase.from_arg_list,
 66        }
 67
 68        NO_PAREN_FUNCTION_PARSERS = {
 69            **Postgres.Parser.NO_PAREN_FUNCTION_PARSERS,
 70            "APPROXIMATE": lambda self: self._parse_approximate_count(),
 71            "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, transaction=True),
 72        }
 73
 74        def _parse_table(
 75            self,
 76            schema: bool = False,
 77            joins: bool = False,
 78            alias_tokens: t.Optional[t.Collection[TokenType]] = None,
 79            parse_bracket: bool = False,
 80            is_db_reference: bool = False,
 81        ) -> t.Optional[exp.Expression]:
 82            # Redshift supports UNPIVOTing SUPER objects, e.g. `UNPIVOT foo.obj[0] AS val AT attr`
 83            unpivot = self._match(TokenType.UNPIVOT)
 84            table = super()._parse_table(
 85                schema=schema,
 86                joins=joins,
 87                alias_tokens=alias_tokens,
 88                parse_bracket=parse_bracket,
 89                is_db_reference=is_db_reference,
 90            )
 91
 92            return self.expression(exp.Pivot, this=table, unpivot=True) if unpivot else table
 93
 94        def _parse_types(
 95            self, check_func: bool = False, schema: bool = False, allow_identifiers: bool = True
 96        ) -> t.Optional[exp.Expression]:
 97            this = super()._parse_types(
 98                check_func=check_func, schema=schema, allow_identifiers=allow_identifiers
 99            )
100
101            if (
102                isinstance(this, exp.DataType)
103                and this.is_type("varchar")
104                and this.expressions
105                and this.expressions[0].this == exp.column("MAX")
106            ):
107                this.set("expressions", [exp.var("MAX")])
108
109            return this
110
111        def _parse_convert(
112            self, strict: bool, safe: t.Optional[bool] = None
113        ) -> t.Optional[exp.Expression]:
114            to = self._parse_types()
115            self._match(TokenType.COMMA)
116            this = self._parse_bitwise()
117            return self.expression(exp.TryCast, this=this, to=to, safe=safe)
118
119        def _parse_approximate_count(self) -> t.Optional[exp.ApproxDistinct]:
120            index = self._index - 1
121            func = self._parse_function()
122
123            if isinstance(func, exp.Count) and isinstance(func.this, exp.Distinct):
124                return self.expression(exp.ApproxDistinct, this=seq_get(func.this.expressions, 0))
125            self._retreat(index)
126            return None
127
128        def _parse_query_modifiers(
129            self, this: t.Optional[exp.Expression]
130        ) -> t.Optional[exp.Expression]:
131            this = super()._parse_query_modifiers(this)
132
133            if this:
134                refs = set()
135
136                for i, join in enumerate(this.args.get("joins", [])):
137                    refs.add(
138                        (
139                            this.args["from"] if i == 0 else this.args["joins"][i - 1]
140                        ).this.alias.lower()
141                    )
142
143                    table = join.this
144                    if isinstance(table, exp.Table) and not join.args.get("on"):
145                        if table.parts[0].name.lower() in refs:
146                            table.replace(table.to_column())
147            return this

149    class Tokenizer(Postgres.Tokenizer):
150        BIT_STRINGS = []
151        HEX_STRINGS = []
152        STRING_ESCAPES = ["\\", "'"]
153
154        KEYWORDS = {
155            **Postgres.Tokenizer.KEYWORDS,
156            "HLLSKETCH": TokenType.HLLSKETCH,
157            "SUPER": TokenType.SUPER,
158            "TOP": TokenType.TOP,
159            "UNLOAD": TokenType.COMMAND,
160            "VARBYTE": TokenType.VARBINARY,
161        }
162
163        # Redshift allows # to appear as a table identifier prefix
164        SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy()
165        SINGLE_TOKENS.pop("#")

167    class Generator(Postgres.Generator):
168        LOCKING_READS_SUPPORTED = False
169        QUERY_HINTS = False
170        VALUES_AS_TABLE = False
171        TZ_TO_WITH_TIME_ZONE = True
172        NVL2_SUPPORTED = True
173        LAST_DAY_SUPPORTS_DATE_PART = False
174
175        TYPE_MAPPING = {
176            **Postgres.Generator.TYPE_MAPPING,
177            exp.DataType.Type.BINARY: "VARBYTE",
178            exp.DataType.Type.INT: "INTEGER",
179            exp.DataType.Type.TIMETZ: "TIME",
180            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
181            exp.DataType.Type.VARBINARY: "VARBYTE",
182        }
183
184        TRANSFORMS = {
185            **Postgres.Generator.TRANSFORMS,
186            exp.Concat: concat_to_dpipe_sql,
187            exp.ConcatWs: concat_ws_to_dpipe_sql,
188            exp.ApproxDistinct: lambda self,
189            e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})",
190            exp.CurrentTimestamp: lambda self, e: (
191                "SYSDATE" if e.args.get("transaction") else "GETDATE()"
192            ),
193            exp.DateAdd: date_delta_sql("DATEADD"),
194            exp.DateDiff: date_delta_sql("DATEDIFF"),
195            exp.DistKeyProperty: lambda self, e: f"DISTKEY({e.name})",
196            exp.DistStyleProperty: lambda self, e: self.naked_property(e),
197            exp.FromBase: rename_func("STRTOL"),
198            exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql,
199            exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
200            exp.GroupConcat: rename_func("LISTAGG"),
201            exp.ParseJSON: rename_func("JSON_PARSE"),
202            exp.Select: transforms.preprocess(
203                [transforms.eliminate_distinct_on, transforms.eliminate_semi_and_anti_joins]
204            ),
205            exp.SortKeyProperty: lambda self,
206            e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})",
207            exp.TableSample: no_tablesample_sql,
208            exp.TsOrDsAdd: date_delta_sql("DATEADD"),
209            exp.TsOrDsDiff: date_delta_sql("DATEDIFF"),
210        }
211
212        # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots
213        TRANSFORMS.pop(exp.Pivot)
214
215        # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres)
216        TRANSFORMS.pop(exp.Pow)
217
218        # Redshift supports ANY_VALUE(..)
219        TRANSFORMS.pop(exp.AnyValue)
220
221        # Redshift supports LAST_DAY(..)
222        TRANSFORMS.pop(exp.LastDay)
223
224        RESERVED_KEYWORDS = {*Postgres.Generator.RESERVED_KEYWORDS, "snapshot", "type"}
225
226        def with_properties(self, properties: exp.Properties) -> str:
227            """Redshift doesn't have `WITH` as part of their with_properties so we remove it"""
228            return self.properties(properties, prefix=" ", suffix="")
229
230        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
231            if expression.is_type(exp.DataType.Type.JSON):
232                # Redshift doesn't support a JSON type, so casting to it is treated as a noop
233                return self.sql(expression, "this")
234
235            return super().cast_sql(expression, safe_prefix=safe_prefix)
236
237        def datatype_sql(self, expression: exp.DataType) -> str:
238            """
239            Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
240            VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
241            without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
242            `TEXT` to `VARCHAR`.
243            """
244            if expression.is_type("text"):
245                expression.set("this", exp.DataType.Type.VARCHAR)
246                precision = expression.args.get("expressions")
247
248                if not precision:
249                    expression.append("expressions", exp.var("MAX"))
250
251            return super().datatype_sql(expression)