Edit on GitHub

sqlglot.generators.redshift

View Source

  1from __future__ import annotations
  2
  3
  4from sqlglot import exp, transforms
  5from sqlglot.dialects.dialect import (
  6    array_concat_sql,
  7    concat_to_dpipe_sql,
  8    concat_ws_to_dpipe_sql,
  9    date_delta_sql,
 10    generatedasidentitycolumnconstraint_sql,
 11    json_extract_segments,
 12    no_tablesample_sql,
 13    rename_func,
 14)
 15from sqlglot.generator import Generator
 16from sqlglot.generators.postgres import PostgresGenerator
 17from sqlglot.helper import seq_get
 18
 19
 20class RedshiftGenerator(PostgresGenerator):
 21    LOCKING_READS_SUPPORTED = False
 22    QUERY_HINTS = False
 23    VALUES_AS_TABLE = False
 24    TZ_TO_WITH_TIME_ZONE = True
 25    NVL2_SUPPORTED = True
 26    LAST_DAY_SUPPORTS_DATE_PART = False
 27    CAN_IMPLEMENT_ARRAY_ANY = False
 28    MULTI_ARG_DISTINCT = True
 29    COPY_PARAMS_ARE_WRAPPED = False
 30    HEX_FUNC = "TO_HEX"
 31    PARSE_JSON_NAME: str | None = "JSON_PARSE"
 32    ARRAY_CONCAT_IS_VAR_LEN = False
 33    SUPPORTS_CONVERT_TIMEZONE = True
 34    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
 35    SUPPORTS_MEDIAN = True
 36    ALTER_SET_TYPE = "TYPE"
 37    SUPPORTS_DECODE_CASE = True
 38    SUPPORTS_BETWEEN_FLAGS = False
 39    LIMIT_FETCH = "LIMIT"
 40    STAR_EXCEPT = "EXCLUDE"
 41    STAR_EXCLUDE_REQUIRES_DERIVED_TABLE = False
 42
 43    # Redshift doesn't have `WITH` as part of their with_properties so we remove it
 44    WITH_PROPERTIES_PREFIX = " "
 45
 46    TYPE_MAPPING = {
 47        **PostgresGenerator.TYPE_MAPPING,
 48        exp.DType.BINARY: "VARBYTE",
 49        exp.DType.BLOB: "VARBYTE",
 50        exp.DType.INT: "INTEGER",
 51        exp.DType.TIMETZ: "TIME",
 52        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
 53        exp.DType.VARBINARY: "VARBYTE",
 54        exp.DType.ROWVERSION: "VARBYTE",
 55    }
 56
 57    TRANSFORMS = {
 58        **{
 59            k: v
 60            for k, v in PostgresGenerator.TRANSFORMS.items()
 61            if k
 62            not in {
 63                exp.Pivot,
 64                exp.ParseJSON,
 65                exp.AnyValue,
 66                exp.LastDay,
 67                exp.SHA2,
 68                exp.Getbit,
 69                exp.Round,
 70                exp.TryCast,
 71            }
 72        },
 73        exp.ArrayConcat: array_concat_sql("ARRAY_CONCAT"),
 74        exp.Concat: concat_to_dpipe_sql,
 75        exp.ConcatWs: concat_ws_to_dpipe_sql,
 76        exp.ApproxDistinct: lambda self, e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})",
 77        exp.CurrentTimestamp: lambda self, e: "SYSDATE" if e.args.get("sysdate") else "GETDATE()",
 78        exp.CurrentUserId: lambda *_: "CURRENT_USER_ID",
 79        exp.DateAdd: date_delta_sql("DATEADD"),
 80        exp.DateDiff: date_delta_sql("DATEDIFF"),
 81        exp.DistKeyProperty: lambda self, e: self.func("DISTKEY", e.this),
 82        exp.DistStyleProperty: lambda self, e: self.naked_property(e),
 83        exp.Explode: lambda self, e: self.explode_sql(e),
 84        exp.FarmFingerprint: rename_func("FARMFINGERPRINT64"),
 85        exp.FromBase: rename_func("STRTOL"),
 86        exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql,
 87        exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
 88        exp.JSONExtractScalar: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
 89        exp.GroupConcat: rename_func("LISTAGG"),
 90        exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
 91        exp.RegexpExtract: rename_func("REGEXP_SUBSTR"),
 92        exp.Select: transforms.preprocess(
 93            [
 94                transforms.eliminate_window_clause,
 95                transforms.eliminate_distinct_on,
 96                transforms.eliminate_semi_and_anti_joins,
 97                transforms.unqualify_unnest,
 98                transforms.unnest_generate_date_array_using_recursive_cte,
 99            ]
100        ),
101        exp.SortKeyProperty: lambda self, e: (
102            f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})"
103        ),
104        exp.StartsWith: lambda self, e: f"{self.sql(e.this)} LIKE {self.sql(e.expression)} || '%'",
105        exp.StringToArray: rename_func("SPLIT_TO_ARRAY"),
106        exp.TableSample: no_tablesample_sql,
107        exp.TsOrDsAdd: date_delta_sql("DATEADD"),
108        exp.TsOrDsDiff: date_delta_sql("DATEDIFF"),
109        exp.UnixToTime: lambda self, e: self._unix_to_time_sql(e),
110        exp.SHA2Digest: lambda self, e: self.func(
111            "SHA2", e.this, e.args.get("length") or exp.Literal.number(256)
112        ),
113    }
114
115    RESERVED_KEYWORDS = {
116        "aes128",
117        "aes256",
118        "all",
119        "allowoverwrite",
120        "analyse",
121        "analyze",
122        "and",
123        "any",
124        "array",
125        "as",
126        "asc",
127        "authorization",
128        "az64",
129        "backup",
130        "between",
131        "binary",
132        "blanksasnull",
133        "both",
134        "bytedict",
135        "bzip2",
136        "case",
137        "cast",
138        "check",
139        "collate",
140        "column",
141        "constraint",
142        "create",
143        "credentials",
144        "cross",
145        "current_date",
146        "current_time",
147        "current_timestamp",
148        "current_user",
149        "current_user_id",
150        "default",
151        "deferrable",
152        "deflate",
153        "defrag",
154        "delta",
155        "delta32k",
156        "desc",
157        "disable",
158        "distinct",
159        "do",
160        "else",
161        "emptyasnull",
162        "enable",
163        "encode",
164        "encrypt     ",
165        "encryption",
166        "end",
167        "except",
168        "explicit",
169        "false",
170        "for",
171        "foreign",
172        "freeze",
173        "from",
174        "full",
175        "globaldict256",
176        "globaldict64k",
177        "grant",
178        "group",
179        "gzip",
180        "having",
181        "identity",
182        "ignore",
183        "ilike",
184        "in",
185        "initially",
186        "inner",
187        "intersect",
188        "interval",
189        "into",
190        "is",
191        "isnull",
192        "join",
193        "leading",
194        "left",
195        "like",
196        "limit",
197        "localtime",
198        "localtimestamp",
199        "lun",
200        "luns",
201        "lzo",
202        "lzop",
203        "minus",
204        "mostly16",
205        "mostly32",
206        "mostly8",
207        "natural",
208        "new",
209        "not",
210        "notnull",
211        "null",
212        "nulls",
213        "off",
214        "offline",
215        "offset",
216        "oid",
217        "old",
218        "on",
219        "only",
220        "open",
221        "or",
222        "order",
223        "outer",
224        "overlaps",
225        "parallel",
226        "partition",
227        "percent",
228        "permissions",
229        "pivot",
230        "placing",
231        "primary",
232        "raw",
233        "readratio",
234        "recover",
235        "references",
236        "rejectlog",
237        "resort",
238        "respect",
239        "restore",
240        "right",
241        "select",
242        "session_user",
243        "similar",
244        "snapshot",
245        "some",
246        "sysdate",
247        "system",
248        "table",
249        "tag",
250        "tdes",
251        "text255",
252        "text32k",
253        "then",
254        "timestamp",
255        "to",
256        "top",
257        "trailing",
258        "true",
259        "truncatecolumns",
260        "type",
261        "union",
262        "unique",
263        "unnest",
264        "unpivot",
265        "user",
266        "using",
267        "verbose",
268        "wallet",
269        "when",
270        "where",
271        "with",
272        "without",
273    }
274
275    def stpoint_sql(self, expression: exp.StPoint) -> str:
276        # ST_POINT only accepts 2 args in Redshift; use ST_MAKEPOINT for 3 or 4 args
277        if expression.args.get("z") or expression.args.get("m"):
278            return self.func(
279                "ST_MAKEPOINT",
280                expression.this,
281                expression.expression,
282                expression.args.get("z"),
283                expression.args.get("m"),
284            )
285        return self.func("ST_POINT", expression.this, expression.expression)
286
287    def arraycontains_sql(self, expression: exp.ArrayContains) -> str:
288        return self.func(
289            "ARRAY_CONTAINS",
290            expression.this,
291            expression.expression,
292            expression.args.get("check_null"),
293        )
294
295    def objecttransform_sql(self, expression: exp.ObjectTransform) -> str:
296        this = self.sql(expression, "this")
297        keep = self.expressions(expression, key="keep", flat=True)
298        set_ = self.expressions(expression, key="set_", flat=True)
299        keep_sql = " KEEP " + keep if keep else ""
300        set_sql = " SET " + set_ if set_ else ""
301        return f"OBJECT_TRANSFORM({this}{keep_sql}{set_sql})"
302
303    def approxquantile_sql(self, expression: exp.ApproxQuantile) -> str:
304        return "APPROXIMATE " + self.sql(
305            exp.WithinGroup(
306                this=exp.PercentileDisc(this=expression.args["quantile"]),
307                expression=exp.Order(expressions=[exp.Ordered(this=expression.this)]),
308            )
309        )
310
311    def unnest_sql(self, expression: exp.Unnest) -> str:
312        args = expression.expressions
313        num_args = len(args)
314
315        if num_args != 1:
316            self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}")
317            return ""
318
319        if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select):
320            self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses")
321            return ""
322
323        arg = self.sql(seq_get(args, 0))
324
325        alias = self.expressions(expression.args.get("alias"), key="columns", flat=True)
326        return f"{arg} AS {alias}" if alias else arg
327
328    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
329        if expression.is_type(exp.DType.JSON):
330            # Redshift doesn't support a JSON type, so casting to it is treated as a noop
331            return self.sql(expression, "this")
332
333        return super().cast_sql(expression, safe_prefix=safe_prefix)
334
335    def datatype_sql(self, expression: exp.DataType) -> str:
336        """
337        Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
338        VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
339        without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
340        `TEXT` to `VARCHAR`.
341        """
342        if expression.is_type("text"):
343            expression.set("this", exp.DType.VARCHAR)
344            precision = expression.args.get("expressions")
345
346            if not precision:
347                expression.append("expressions", exp.var("MAX"))
348
349        return super().datatype_sql(expression)
350
351    def alterset_sql(self, expression: exp.AlterSet) -> str:
352        exprs = self.expressions(expression, flat=True)
353        exprs = f" TABLE PROPERTIES ({exprs})" if exprs else ""
354        location = self.sql(expression, "location")
355        location = f" LOCATION {location}" if location else ""
356        file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
357        file_format = f" FILE FORMAT {file_format}" if file_format else ""
358
359        return f"SET{exprs}{location}{file_format}"
360
361    def array_sql(self, expression: exp.Array) -> str:
362        if expression.args.get("bracket_notation"):
363            return super().array_sql(expression)
364
365        return rename_func("ARRAY")(self, expression)
366
367    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
368        return Generator.ignorenulls_sql(self, expression)
369
370    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
371        return Generator.respectnulls_sql(self, expression)
372
373    def explode_sql(self, expression: exp.Explode) -> str:
374        self.unsupported("Unsupported EXPLODE() function")
375        return ""
376
377    def _unix_to_time_sql(self, expression: exp.UnixToTime) -> str:
378        scale = expression.args.get("scale")
379        this = self.sql(expression.this)
380
381        if scale is not None and scale != exp.UnixToTime.SECONDS and scale.is_int:
382            this = f"({this} / POWER(10, {scale.to_py()}))"
383
384        return f"(TIMESTAMP 'epoch' + {this} * INTERVAL '1 SECOND')"

class RedshiftGenerator(sqlglot.generators.postgres.PostgresGenerator): View Source

 21class RedshiftGenerator(PostgresGenerator):
 22    LOCKING_READS_SUPPORTED = False
 23    QUERY_HINTS = False
 24    VALUES_AS_TABLE = False
 25    TZ_TO_WITH_TIME_ZONE = True
 26    NVL2_SUPPORTED = True
 27    LAST_DAY_SUPPORTS_DATE_PART = False
 28    CAN_IMPLEMENT_ARRAY_ANY = False
 29    MULTI_ARG_DISTINCT = True
 30    COPY_PARAMS_ARE_WRAPPED = False
 31    HEX_FUNC = "TO_HEX"
 32    PARSE_JSON_NAME: str | None = "JSON_PARSE"
 33    ARRAY_CONCAT_IS_VAR_LEN = False
 34    SUPPORTS_CONVERT_TIMEZONE = True
 35    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
 36    SUPPORTS_MEDIAN = True
 37    ALTER_SET_TYPE = "TYPE"
 38    SUPPORTS_DECODE_CASE = True
 39    SUPPORTS_BETWEEN_FLAGS = False
 40    LIMIT_FETCH = "LIMIT"
 41    STAR_EXCEPT = "EXCLUDE"
 42    STAR_EXCLUDE_REQUIRES_DERIVED_TABLE = False
 43
 44    # Redshift doesn't have `WITH` as part of their with_properties so we remove it
 45    WITH_PROPERTIES_PREFIX = " "
 46
 47    TYPE_MAPPING = {
 48        **PostgresGenerator.TYPE_MAPPING,
 49        exp.DType.BINARY: "VARBYTE",
 50        exp.DType.BLOB: "VARBYTE",
 51        exp.DType.INT: "INTEGER",
 52        exp.DType.TIMETZ: "TIME",
 53        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
 54        exp.DType.VARBINARY: "VARBYTE",
 55        exp.DType.ROWVERSION: "VARBYTE",
 56    }
 57
 58    TRANSFORMS = {
 59        **{
 60            k: v
 61            for k, v in PostgresGenerator.TRANSFORMS.items()
 62            if k
 63            not in {
 64                exp.Pivot,
 65                exp.ParseJSON,
 66                exp.AnyValue,
 67                exp.LastDay,
 68                exp.SHA2,
 69                exp.Getbit,
 70                exp.Round,
 71                exp.TryCast,
 72            }
 73        },
 74        exp.ArrayConcat: array_concat_sql("ARRAY_CONCAT"),
 75        exp.Concat: concat_to_dpipe_sql,
 76        exp.ConcatWs: concat_ws_to_dpipe_sql,
 77        exp.ApproxDistinct: lambda self, e: f"APPROXIMATE COUNT(DISTINCT {self.sql(e, 'this')})",
 78        exp.CurrentTimestamp: lambda self, e: "SYSDATE" if e.args.get("sysdate") else "GETDATE()",
 79        exp.CurrentUserId: lambda *_: "CURRENT_USER_ID",
 80        exp.DateAdd: date_delta_sql("DATEADD"),
 81        exp.DateDiff: date_delta_sql("DATEDIFF"),
 82        exp.DistKeyProperty: lambda self, e: self.func("DISTKEY", e.this),
 83        exp.DistStyleProperty: lambda self, e: self.naked_property(e),
 84        exp.Explode: lambda self, e: self.explode_sql(e),
 85        exp.FarmFingerprint: rename_func("FARMFINGERPRINT64"),
 86        exp.FromBase: rename_func("STRTOL"),
 87        exp.GeneratedAsIdentityColumnConstraint: generatedasidentitycolumnconstraint_sql,
 88        exp.JSONExtract: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
 89        exp.JSONExtractScalar: json_extract_segments("JSON_EXTRACT_PATH_TEXT"),
 90        exp.GroupConcat: rename_func("LISTAGG"),
 91        exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
 92        exp.RegexpExtract: rename_func("REGEXP_SUBSTR"),
 93        exp.Select: transforms.preprocess(
 94            [
 95                transforms.eliminate_window_clause,
 96                transforms.eliminate_distinct_on,
 97                transforms.eliminate_semi_and_anti_joins,
 98                transforms.unqualify_unnest,
 99                transforms.unnest_generate_date_array_using_recursive_cte,
100            ]
101        ),
102        exp.SortKeyProperty: lambda self, e: (
103            f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})"
104        ),
105        exp.StartsWith: lambda self, e: f"{self.sql(e.this)} LIKE {self.sql(e.expression)} || '%'",
106        exp.StringToArray: rename_func("SPLIT_TO_ARRAY"),
107        exp.TableSample: no_tablesample_sql,
108        exp.TsOrDsAdd: date_delta_sql("DATEADD"),
109        exp.TsOrDsDiff: date_delta_sql("DATEDIFF"),
110        exp.UnixToTime: lambda self, e: self._unix_to_time_sql(e),
111        exp.SHA2Digest: lambda self, e: self.func(
112            "SHA2", e.this, e.args.get("length") or exp.Literal.number(256)
113        ),
114    }
115
116    RESERVED_KEYWORDS = {
117        "aes128",
118        "aes256",
119        "all",
120        "allowoverwrite",
121        "analyse",
122        "analyze",
123        "and",
124        "any",
125        "array",
126        "as",
127        "asc",
128        "authorization",
129        "az64",
130        "backup",
131        "between",
132        "binary",
133        "blanksasnull",
134        "both",
135        "bytedict",
136        "bzip2",
137        "case",
138        "cast",
139        "check",
140        "collate",
141        "column",
142        "constraint",
143        "create",
144        "credentials",
145        "cross",
146        "current_date",
147        "current_time",
148        "current_timestamp",
149        "current_user",
150        "current_user_id",
151        "default",
152        "deferrable",
153        "deflate",
154        "defrag",
155        "delta",
156        "delta32k",
157        "desc",
158        "disable",
159        "distinct",
160        "do",
161        "else",
162        "emptyasnull",
163        "enable",
164        "encode",
165        "encrypt     ",
166        "encryption",
167        "end",
168        "except",
169        "explicit",
170        "false",
171        "for",
172        "foreign",
173        "freeze",
174        "from",
175        "full",
176        "globaldict256",
177        "globaldict64k",
178        "grant",
179        "group",
180        "gzip",
181        "having",
182        "identity",
183        "ignore",
184        "ilike",
185        "in",
186        "initially",
187        "inner",
188        "intersect",
189        "interval",
190        "into",
191        "is",
192        "isnull",
193        "join",
194        "leading",
195        "left",
196        "like",
197        "limit",
198        "localtime",
199        "localtimestamp",
200        "lun",
201        "luns",
202        "lzo",
203        "lzop",
204        "minus",
205        "mostly16",
206        "mostly32",
207        "mostly8",
208        "natural",
209        "new",
210        "not",
211        "notnull",
212        "null",
213        "nulls",
214        "off",
215        "offline",
216        "offset",
217        "oid",
218        "old",
219        "on",
220        "only",
221        "open",
222        "or",
223        "order",
224        "outer",
225        "overlaps",
226        "parallel",
227        "partition",
228        "percent",
229        "permissions",
230        "pivot",
231        "placing",
232        "primary",
233        "raw",
234        "readratio",
235        "recover",
236        "references",
237        "rejectlog",
238        "resort",
239        "respect",
240        "restore",
241        "right",
242        "select",
243        "session_user",
244        "similar",
245        "snapshot",
246        "some",
247        "sysdate",
248        "system",
249        "table",
250        "tag",
251        "tdes",
252        "text255",
253        "text32k",
254        "then",
255        "timestamp",
256        "to",
257        "top",
258        "trailing",
259        "true",
260        "truncatecolumns",
261        "type",
262        "union",
263        "unique",
264        "unnest",
265        "unpivot",
266        "user",
267        "using",
268        "verbose",
269        "wallet",
270        "when",
271        "where",
272        "with",
273        "without",
274    }
275
276    def stpoint_sql(self, expression: exp.StPoint) -> str:
277        # ST_POINT only accepts 2 args in Redshift; use ST_MAKEPOINT for 3 or 4 args
278        if expression.args.get("z") or expression.args.get("m"):
279            return self.func(
280                "ST_MAKEPOINT",
281                expression.this,
282                expression.expression,
283                expression.args.get("z"),
284                expression.args.get("m"),
285            )
286        return self.func("ST_POINT", expression.this, expression.expression)
287
288    def arraycontains_sql(self, expression: exp.ArrayContains) -> str:
289        return self.func(
290            "ARRAY_CONTAINS",
291            expression.this,
292            expression.expression,
293            expression.args.get("check_null"),
294        )
295
296    def objecttransform_sql(self, expression: exp.ObjectTransform) -> str:
297        this = self.sql(expression, "this")
298        keep = self.expressions(expression, key="keep", flat=True)
299        set_ = self.expressions(expression, key="set_", flat=True)
300        keep_sql = " KEEP " + keep if keep else ""
301        set_sql = " SET " + set_ if set_ else ""
302        return f"OBJECT_TRANSFORM({this}{keep_sql}{set_sql})"
303
304    def approxquantile_sql(self, expression: exp.ApproxQuantile) -> str:
305        return "APPROXIMATE " + self.sql(
306            exp.WithinGroup(
307                this=exp.PercentileDisc(this=expression.args["quantile"]),
308                expression=exp.Order(expressions=[exp.Ordered(this=expression.this)]),
309            )
310        )
311
312    def unnest_sql(self, expression: exp.Unnest) -> str:
313        args = expression.expressions
314        num_args = len(args)
315
316        if num_args != 1:
317            self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}")
318            return ""
319
320        if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select):
321            self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses")
322            return ""
323
324        arg = self.sql(seq_get(args, 0))
325
326        alias = self.expressions(expression.args.get("alias"), key="columns", flat=True)
327        return f"{arg} AS {alias}" if alias else arg
328
329    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
330        if expression.is_type(exp.DType.JSON):
331            # Redshift doesn't support a JSON type, so casting to it is treated as a noop
332            return self.sql(expression, "this")
333
334        return super().cast_sql(expression, safe_prefix=safe_prefix)
335
336    def datatype_sql(self, expression: exp.DataType) -> str:
337        """
338        Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
339        VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
340        without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
341        `TEXT` to `VARCHAR`.
342        """
343        if expression.is_type("text"):
344            expression.set("this", exp.DType.VARCHAR)
345            precision = expression.args.get("expressions")
346
347            if not precision:
348                expression.append("expressions", exp.var("MAX"))
349
350        return super().datatype_sql(expression)
351
352    def alterset_sql(self, expression: exp.AlterSet) -> str:
353        exprs = self.expressions(expression, flat=True)
354        exprs = f" TABLE PROPERTIES ({exprs})" if exprs else ""
355        location = self.sql(expression, "location")
356        location = f" LOCATION {location}" if location else ""
357        file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
358        file_format = f" FILE FORMAT {file_format}" if file_format else ""
359
360        return f"SET{exprs}{location}{file_format}"
361
362    def array_sql(self, expression: exp.Array) -> str:
363        if expression.args.get("bracket_notation"):
364            return super().array_sql(expression)
365
366        return rename_func("ARRAY")(self, expression)
367
368    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
369        return Generator.ignorenulls_sql(self, expression)
370
371    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
372        return Generator.respectnulls_sql(self, expression)
373
374    def explode_sql(self, expression: exp.Explode) -> str:
375        self.unsupported("Unsupported EXPLODE() function")
376        return ""
377
378    def _unix_to_time_sql(self, expression: exp.UnixToTime) -> str:
379        scale = expression.args.get("scale")
380        this = self.sql(expression.this)
381
382        if scale is not None and scale != exp.UnixToTime.SECONDS and scale.is_int:
383            this = f"({this} / POWER(10, {scale.to_py()}))"
384
385        return f"(TIMESTAMP 'epoch' + {this} * INTERVAL '1 SECOND')"

Generator converts a given syntax tree to the corresponding SQL string.

Arguments:

pretty: Whether to format the produced SQL string. Default: False.
identify: Determines when an identifier should be quoted. Possible values are: False (default): Never quote, except in cases where it's mandatory by the dialect. True: Always quote except for specials cases. 'safe': Only quote identifiers that are case insensitive.
normalize: Whether to normalize identifiers to lowercase. Default: False.
pad: The pad size in a formatted string. For example, this affects the indentation of a projection in a query, relative to its nesting level. Default: 2.
indent: The indentation size in a formatted string. For example, this affects the indentation of subqueries and filters under a WHERE clause. Default: 2.
normalize_functions: How to normalize function names. Possible values are: "upper" or True (default): Convert names to uppercase. "lower": Convert names to lowercase. False: Disables function name normalization.
unsupported_level: Determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
max_unsupported: Maximum number of unsupported messages to include in a raised UnsupportedError. This is only relevant if unsupported_level is ErrorLevel.RAISE. Default: 3
leading_comma: Whether the comma is leading or trailing in select expressions. This is only relevant when generating in pretty mode. Default: False
max_text_width: The max number of characters in a segment before creating new lines in pretty mode. The default is on the smaller end because the length only represents a segment and not the true line length. Default: 80
comments: Whether to preserve comments in the output SQL code. Default: True

LOCKING_READS_SUPPORTED = False

QUERY_HINTS = False

VALUES_AS_TABLE = False

TZ_TO_WITH_TIME_ZONE = True

NVL2_SUPPORTED = True

LAST_DAY_SUPPORTS_DATE_PART = False

CAN_IMPLEMENT_ARRAY_ANY = False

MULTI_ARG_DISTINCT = True

COPY_PARAMS_ARE_WRAPPED = False

HEX_FUNC = 'TO_HEX'

PARSE_JSON_NAME: str | None = 'JSON_PARSE'

ARRAY_CONCAT_IS_VAR_LEN = False

SUPPORTS_CONVERT_TIMEZONE = True

EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False

SUPPORTS_MEDIAN = True

ALTER_SET_TYPE = 'TYPE'

SUPPORTS_DECODE_CASE = True

SUPPORTS_BETWEEN_FLAGS = False

LIMIT_FETCH = 'LIMIT'

STAR_EXCEPT = 'EXCLUDE'

STAR_EXCLUDE_REQUIRES_DERIVED_TABLE = False

WITH_PROPERTIES_PREFIX = ' '

TYPE_MAPPING = {<DType.DATETIME2: 'DATETIME2'>: 'TIMESTAMP', <DType.NCHAR: 'NCHAR'>: 'CHAR', <DType.NVARCHAR: 'NVARCHAR'>: 'VARCHAR', <DType.MEDIUMTEXT: 'MEDIUMTEXT'>: 'TEXT', <DType.LONGTEXT: 'LONGTEXT'>: 'TEXT', <DType.TINYTEXT: 'TINYTEXT'>: 'TEXT', <DType.BLOB: 'BLOB'>: 'VARBYTE', <DType.MEDIUMBLOB: 'MEDIUMBLOB'>: 'BLOB', <DType.LONGBLOB: 'LONGBLOB'>: 'BLOB', <DType.TINYBLOB: 'TINYBLOB'>: 'BLOB', <DType.INET: 'INET'>: 'INET', <DType.ROWVERSION: 'ROWVERSION'>: 'VARBYTE', <DType.SMALLDATETIME: 'SMALLDATETIME'>: 'TIMESTAMP', <DType.TINYINT: 'TINYINT'>: 'SMALLINT', <DType.FLOAT: 'FLOAT'>: 'REAL', <DType.DOUBLE: 'DOUBLE'>: 'DOUBLE PRECISION', <DType.BINARY: 'BINARY'>: 'VARBYTE', <DType.VARBINARY: 'VARBINARY'>: 'VARBYTE', <DType.DATETIME: 'DATETIME'>: 'TIMESTAMP', <DType.TIMESTAMPNTZ: 'TIMESTAMPNTZ'>: 'TIMESTAMP', <DType.INT: 'INT'>: 'INTEGER', <DType.TIMETZ: 'TIMETZ'>: 'TIME', <DType.TIMESTAMPTZ: 'TIMESTAMPTZ'>: 'TIMESTAMP'}

TRANSFORMS = {<class 'sqlglot.expressions.query.JSONPathKey'>: <function json_path_key_only_name>, <class 'sqlglot.expressions.query.JSONPathRoot'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.query.JSONPathSubscript'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.core.Adjacent'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.AllowedValuesProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.AnalyzeColumns'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.AnalyzeWith'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.array.ArrayContainsAll'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.array.ArrayOverlaps'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.AssumeColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.AutoRefreshProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.BackupProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.CaseSpecificColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.math.Ceil'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.CharacterSetColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.CharacterSetProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.ClusteredColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.CollateColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.functions.ConnectByRoot'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.string.ConvertToCharset'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.CopyGrantsProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.CredentialsProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.functions.CurrentCatalog'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.functions.SessionUser'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.DateFormatColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.DefaultColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ApiProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ApplicationProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.CatalogProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ComputeProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.DatabaseProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.DynamicProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.EmptyProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.EncodeColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.EndStatement'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.EnviromentProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.HandlerProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ParameterStyleProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.EphemeralColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.ExcludeColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ExecuteAsProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.Except'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ExternalProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.math.Floor'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.Get'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.GlobalProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.HeapProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.HybridProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.IcebergProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.InheritsProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.InlineLengthColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.InputModelProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.Intersect'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.datatypes.IntervalSpan'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.functions.Int64'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.json.JSONBContainsAnyTopKeys'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.json.JSONBContainsAllTopKeys'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.json.JSONBDeleteAtPath'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.json.JSONObject'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.json.JSONObjectAgg'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.properties.LanguageProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.LocationProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.LogProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.MaskingProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.MaterializedProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.functions.NetFunc'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.NetworkProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.NonClusteredColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.NoPrimaryIndexProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.NotForReplicationColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.OnCommitProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.OnProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.OnUpdateColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.core.Operator'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.OutputModelProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.core.ExtendsLeft'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.core.ExtendsRight'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.PathColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.PartitionedByBucket'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.PartitionByTruncate'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.core.PivotAny'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.array.PositionalColumn'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.ProjectionPolicyColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.InvisibleColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.ZeroFillColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.Put'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.RemoteWithConnectionModelProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ReturnsProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.RowAccessProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.core.SafeFunc'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SampleProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SecureProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SecurityIntegrationProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SetConfigProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SetProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SettingsProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SharingProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SqlReadWriteProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.SqlSecurityProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.StabilityProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.Stream'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.StreamingTableProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.StrictProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.ddl.SwapTable'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.TableColumn'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.Tags'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.TemporaryProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.TitleColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.array.ToMap'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ToTableProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.TransformModelProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.TransientProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.VirtualProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.ddl.TriggerExecute'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.Union'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.UnloggedProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.UsingTemplateProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.UsingData'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.UppercaseColumnConstraint'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.temporal.UtcDate'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.temporal.UtcTime'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.temporal.UtcTimestamp'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.query.Variadic'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.array.VarMap'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ViewAttributeProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.VolatileProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.WithJournalTableProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.WithProcedureOptions'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.WithSchemaBindingProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.constraints.WithOperator'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.properties.ForceProperty'>: <function Generator.<lambda>>, <class 'sqlglot.expressions.array.ArrayConcat'>: <function array_concat_sql.<locals>._array_concat_sql>, <class 'sqlglot.expressions.array.ArrayFilter'>: <function filter_array_using_unnest>, <class 'sqlglot.expressions.array.ArrayAppend'>: <function array_append_sql.<locals>._array_append_sql>, <class 'sqlglot.expressions.array.ArrayPrepend'>: <function array_append_sql.<locals>._array_append_sql>, <class 'sqlglot.expressions.math.BitwiseAndAgg'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.math.BitwiseOrAgg'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.core.BitwiseXor'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.math.BitwiseXorAgg'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.query.ColumnDef'>: <function preprocess.<locals>._to_sql>, <class 'sqlglot.expressions.temporal.CurrentDate'>: <function no_paren_current_date_sql>, <class 'sqlglot.expressions.temporal.CurrentTimestamp'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.functions.CurrentUser'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.functions.CurrentVersion'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.temporal.DateAdd'>: <function date_delta_sql.<locals>._delta_sql>, <class 'sqlglot.expressions.temporal.DateDiff'>: <function date_delta_sql.<locals>._delta_sql>, <class 'sqlglot.expressions.temporal.DateStrToDate'>: <function datestrtodate_sql>, <class 'sqlglot.expressions.temporal.DateSub'>: <function _date_add_sql.<locals>.func>, <class 'sqlglot.expressions.array.Explode'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.array.ExplodingGenerateSeries'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.array.GenerateSeries'>: <function generate_series_sql.<locals>._generate_series_sql>, <class 'sqlglot.expressions.aggregate.GroupConcat'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.core.IntDiv'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.json.JSONArrayAgg'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.json.JSONExtract'>: <function json_extract_segments.<locals>._json_extract_segments>, <class 'sqlglot.expressions.json.JSONExtractScalar'>: <function json_extract_segments.<locals>._json_extract_segments>, <class 'sqlglot.expressions.json.JSONBExtract'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.json.JSONBExtractScalar'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.json.JSONBContains'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.aggregate.LogicalOr'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.aggregate.LogicalAnd'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.aggregate.Max'>: <function max_or_greatest>, <class 'sqlglot.expressions.array.MapFromEntries'>: <function no_map_from_entries_sql>, <class 'sqlglot.expressions.aggregate.Min'>: <function min_or_least>, <class 'sqlglot.expressions.dml.Merge'>: <function merge_without_target_sql>, <class 'sqlglot.expressions.properties.PartitionedByProperty'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.aggregate.PercentileCont'>: <function preprocess.<locals>._to_sql>, <class 'sqlglot.expressions.aggregate.PercentileDisc'>: <function preprocess.<locals>._to_sql>, <class 'sqlglot.expressions.functions.Rand'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.core.RegexpLike'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.string.RegexpILike'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.string.RegexpReplace'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.query.Select'>: <function preprocess.<locals>._to_sql>, <class 'sqlglot.expressions.string.SHA2Digest'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.string.StrPosition'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.temporal.StrToDate'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.temporal.StrToTime'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.array.StructExtract'>: <function struct_extract_sql>, <class 'sqlglot.expressions.string.Substring'>: <function _substring_sql>, <class 'sqlglot.expressions.temporal.TimeFromParts'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.temporal.TimestampFromParts'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.temporal.TimestampTrunc'>: <function timestamptrunc_sql.<locals>._timestamptrunc_sql>, <class 'sqlglot.expressions.temporal.TimeStrToTime'>: <function timestrtotime_sql>, <class 'sqlglot.expressions.temporal.TimeToStr'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.string.ToChar'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.string.Trim'>: <function trim_sql>, <class 'sqlglot.expressions.temporal.TsOrDsAdd'>: <function date_delta_sql.<locals>._delta_sql>, <class 'sqlglot.expressions.temporal.TsOrDsDiff'>: <function date_delta_sql.<locals>._delta_sql>, <class 'sqlglot.expressions.temporal.UnixToTime'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.functions.Uuid'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.temporal.TimeToUnix'>: <function PostgresGenerator.<lambda>>, <class 'sqlglot.expressions.aggregate.VariancePop'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.aggregate.Variance'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.core.Xor'>: <function bool_xor_sql>, <class 'sqlglot.expressions.string.Unicode'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.string.Levenshtein'>: <function _levenshtein_sql>, <class 'sqlglot.expressions.json.JSONBObjectAgg'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.aggregate.CountIf'>: <function count_if_to_sum>, <class 'sqlglot.expressions.string.Concat'>: <function concat_to_dpipe_sql>, <class 'sqlglot.expressions.string.ConcatWs'>: <function concat_ws_to_dpipe_sql>, <class 'sqlglot.expressions.core.ApproxDistinct'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.functions.CurrentUserId'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.properties.DistKeyProperty'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.properties.DistStyleProperty'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.string.FarmFingerprint'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.string.FromBase'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.constraints.GeneratedAsIdentityColumnConstraint'>: <function generatedasidentitycolumnconstraint_sql>, <class 'sqlglot.expressions.string.Hex'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.string.RegexpExtract'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.properties.SortKeyProperty'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.string.StartsWith'>: <function RedshiftGenerator.<lambda>>, <class 'sqlglot.expressions.array.StringToArray'>: <function rename_func.<locals>.<lambda>>, <class 'sqlglot.expressions.query.TableSample'>: <function no_tablesample_sql>}

RESERVED_KEYWORDS = {'bytedict', 'authorization', 'nulls', 'limit', 'join', 'partition', 'tdes', 'desc', 'create', 'asc', 'parallel', 'emptyasnull', 'permissions', 'as', 'lun', 'globaldict256', 'localtimestamp', 'having', 'off', 'explicit', 'left', 'interval', 'text255', 'unpivot', 'encryption', 'ignore', 'false', 'full', 'into', 'snapshot', 'analyse', 'analyze', 'then', 'allowoverwrite', 'similar', 'current_user', 'encrypt ', 'minus', 'to', 'system', 'union', 'initially', 'delta', 'offline', 'isnull', 'backup', 'user', 'oid', 'session_user', 'is', 'default', 'constraint', 'verbose', 'recover', 'resort', 'credentials', 'lzop', 'both', 'any', 'leading', 'restore', 'truncatecolumns', 'without', 'sysdate', 'current_date', 'wallet', 'blanksasnull', 'readratio', 'check', 'placing', 'order', 'deflate', 'overlaps', 'primary', 'aes128', 'ilike', 'current_user_id', 'aes256', 'az64', 'respect', 'gzip', 'using', 'select', 'like', 'right', 'table', 'raw', 'natural', 'with', 'new', 'all', 'cross', 'rejectlog', 'true', 'array', 'binary', 'collate', 'open', 'group', 'between', 'references', 'delta32k', 'current_timestamp', 'localtime', 'text32k', 'top', 'or', 'outer', 'defrag', 'except', 'freeze', 'some', 'only', 'column', 'mostly8', 'else', 'mostly32', 'bzip2', 'encode', 'offset', 'disable', 'null', 'distinct', 'grant', 'end', 'cast', 'pivot', 'where', 'trailing', 'percent', 'tag', 'from', 'type', 'current_time', 'unnest', 'unique', 'inner', 'intersect', 'not', 'on', 'do', 'in', 'deferrable', 'old', 'case', 'identity', 'mostly16', 'when', 'for', 'globaldict64k', 'timestamp', 'lzo', 'luns', 'and', 'enable', 'notnull', 'foreign'}

def stpoint_sql(self, expression: sqlglot.expressions.array.StPoint) -> str: View Source

276    def stpoint_sql(self, expression: exp.StPoint) -> str:
277        # ST_POINT only accepts 2 args in Redshift; use ST_MAKEPOINT for 3 or 4 args
278        if expression.args.get("z") or expression.args.get("m"):
279            return self.func(
280                "ST_MAKEPOINT",
281                expression.this,
282                expression.expression,
283                expression.args.get("z"),
284                expression.args.get("m"),
285            )
286        return self.func("ST_POINT", expression.this, expression.expression)

def arraycontains_sql(self, expression: sqlglot.expressions.array.ArrayContains) -> str: View Source

288    def arraycontains_sql(self, expression: exp.ArrayContains) -> str:
289        return self.func(
290            "ARRAY_CONTAINS",
291            expression.this,
292            expression.expression,
293            expression.args.get("check_null"),
294        )

def objecttransform_sql(self, expression: sqlglot.expressions.functions.ObjectTransform) -> str: View Source

296    def objecttransform_sql(self, expression: exp.ObjectTransform) -> str:
297        this = self.sql(expression, "this")
298        keep = self.expressions(expression, key="keep", flat=True)
299        set_ = self.expressions(expression, key="set_", flat=True)
300        keep_sql = " KEEP " + keep if keep else ""
301        set_sql = " SET " + set_ if set_ else ""
302        return f"OBJECT_TRANSFORM({this}{keep_sql}{set_sql})"

def approxquantile_sql(self, expression: sqlglot.expressions.aggregate.ApproxQuantile) -> str: View Source

304    def approxquantile_sql(self, expression: exp.ApproxQuantile) -> str:
305        return "APPROXIMATE " + self.sql(
306            exp.WithinGroup(
307                this=exp.PercentileDisc(this=expression.args["quantile"]),
308                expression=exp.Order(expressions=[exp.Ordered(this=expression.this)]),
309            )
310        )

def unnest_sql(self, expression: sqlglot.expressions.array.Unnest) -> str: View Source

312    def unnest_sql(self, expression: exp.Unnest) -> str:
313        args = expression.expressions
314        num_args = len(args)
315
316        if num_args != 1:
317            self.unsupported(f"Unsupported number of arguments in UNNEST: {num_args}")
318            return ""
319
320        if isinstance(expression.find_ancestor(exp.From, exp.Join, exp.Select), exp.Select):
321            self.unsupported("Unsupported UNNEST when not used in FROM/JOIN clauses")
322            return ""
323
324        arg = self.sql(seq_get(args, 0))
325
326        alias = self.expressions(expression.args.get("alias"), key="columns", flat=True)
327        return f"{arg} AS {alias}" if alias else arg

def cast_sql( self, expression: sqlglot.expressions.functions.Cast, safe_prefix: str | None = None) -> str: View Source

329    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
330        if expression.is_type(exp.DType.JSON):
331            # Redshift doesn't support a JSON type, so casting to it is treated as a noop
332            return self.sql(expression, "this")
333
334        return super().cast_sql(expression, safe_prefix=safe_prefix)

def datatype_sql(self, expression: sqlglot.expressions.datatypes.DataType) -> str: View Source

336    def datatype_sql(self, expression: exp.DataType) -> str:
337        """
338        Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
339        VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
340        without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
341        `TEXT` to `VARCHAR`.
342        """
343        if expression.is_type("text"):
344            expression.set("this", exp.DType.VARCHAR)
345            precision = expression.args.get("expressions")
346
347            if not precision:
348                expression.append("expressions", exp.var("MAX"))
349
350        return super().datatype_sql(expression)

Redshift converts the TEXT data type to VARCHAR(255) by default when people more generally mean VARCHAR of max length which is VARCHAR(max) in Redshift. Therefore if we get a TEXT data type without precision we convert it to VARCHAR(max) and if it does have precision then we just convert TEXT to VARCHAR.

def alterset_sql(self, expression: sqlglot.expressions.ddl.AlterSet) -> str: View Source

352    def alterset_sql(self, expression: exp.AlterSet) -> str:
353        exprs = self.expressions(expression, flat=True)
354        exprs = f" TABLE PROPERTIES ({exprs})" if exprs else ""
355        location = self.sql(expression, "location")
356        location = f" LOCATION {location}" if location else ""
357        file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
358        file_format = f" FILE FORMAT {file_format}" if file_format else ""
359
360        return f"SET{exprs}{location}{file_format}"

def array_sql(self, expression: sqlglot.expressions.array.Array) -> str: View Source

362    def array_sql(self, expression: exp.Array) -> str:
363        if expression.args.get("bracket_notation"):
364            return super().array_sql(expression)
365
366        return rename_func("ARRAY")(self, expression)

def ignorenulls_sql(self, expression: sqlglot.expressions.core.IgnoreNulls) -> str: View Source

368    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
369        return Generator.ignorenulls_sql(self, expression)

def respectnulls_sql(self, expression: sqlglot.expressions.core.RespectNulls) -> str: View Source

371    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
372        return Generator.respectnulls_sql(self, expression)

def explode_sql(self, expression: sqlglot.expressions.array.Explode) -> str: View Source

374    def explode_sql(self, expression: exp.Explode) -> str:
375        self.unsupported("Unsupported EXPLODE() function")
376        return ""

Inherited Members

sqlglot.generator.Generator: Generator; NULL_ORDERING_SUPPORTED; WINDOW_FUNCS_WITH_NULL_ORDERING; IGNORE_NULLS_IN_FUNC; IGNORE_NULLS_BEFORE_ORDER; WRAP_DERIVED_VALUES; CREATE_FUNCTION_RETURN_AS; MATCHED_BY_SOURCE; SUPPORTS_MERGE_WHERE; INTERVAL_ALLOWS_PLURAL_FORM; LIMIT_ONLY_LITERALS; GROUPINGS_SEP; INDEX_ON; DIRECTED_JOINS; QUERY_HINT_SEP; IS_BOOL_ALLOWED; DUPLICATE_KEY_UPDATE_WITH_SET; LIMIT_IS_TOP; RETURNING_END; EXTRACT_ALLOWS_QUOTES; ALTER_TABLE_INCLUDE_COLUMN_KEYWORD; UNNEST_WITH_ORDINALITY; AGGREGATE_FILTER_SUPPORTED; SEMI_ANTI_JOIN_WITH_SIDE; COMPUTED_COLUMN_WITH_TYPE; SUPPORTS_TABLE_COPY; TABLESAMPLE_REQUIRES_PARENS; TABLESAMPLE_KEYWORDS; TABLESAMPLE_WITH_METHOD; COLLATE_IS_FUNC; DATA_TYPE_SPECIFIERS_ALLOWED; ENSURE_BOOLS; CTE_RECURSIVE_KEYWORD_REQUIRED; SUPPORTS_SINGLE_ARG_CONCAT; SUPPORTS_TABLE_ALIAS_COLUMNS; SUPPORTS_NAMED_CTE_COLUMNS; UNPIVOT_ALIASES_ARE_IDENTIFIERS; JSON_KEY_VALUE_PAIR_SEP; INSERT_OVERWRITE; SUPPORTS_CREATE_TABLE_LIKE; SUPPORTS_MODIFY_COLUMN; SUPPORTS_CHANGE_COLUMN; JSON_PATH_BRACKETED_KEY_SUPPORTED; JSON_PATH_SINGLE_QUOTE_ESCAPE; SUPPORTS_TO_NUMBER; SET_OP_MODIFIERS; COPY_PARAMS_EQ_REQUIRED; UNICODE_SUBSTITUTE; QUOTE_JSON_PATH; PAD_FILL_PATTERN_IS_REQUIRED; SUPPORTS_EXPLODING_PROJECTIONS; SUPPORTS_UNIX_SECONDS; ALTER_SET_WRAPPED; NORMALIZE_EXTRACT_DATE_PARTS; ARRAY_SIZE_NAME; SUPPORTS_LIKE_QUANTIFIERS; MATCH_AGAINST_TABLE_PREFIX; SET_ASSIGNMENT_REQUIRES_VARIABLE_KEYWORD; DECLARE_DEFAULT_ASSIGNMENT; UPDATE_STATEMENT_SUPPORTS_FROM; SUPPORTS_DROP_ALTER_ICEBERG_PROPERTY; UNSUPPORTED_TYPES; TYPE_PARAM_SETTINGS; TIME_PART_SINGULARS; TOKEN_MAPPING; STRUCT_DELIMITER; EXPRESSION_PRECEDES_PROPERTIES_CREATABLES; WITH_SEPARATED_COMMENTS; EXCLUDE_COMMENTS; UNWRAPPED_INTERVAL_VALUES; PARAMETERIZABLE_TEXT_TYPES; EXPRESSIONS_WITHOUT_NESTED_CTES; RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS; SAFE_JSON_PATH_KEY_RE; SENTINEL_LINE_BREAK; pretty; identify; normalize; pad; unsupported_level; max_unsupported; leading_comma; max_text_width; comments; dialect; normalize_functions; unsupported_messages; generate; preprocess; unsupported; sep; seg; sanitize_comment; maybe_comment; wrap; no_identify; normalize_func; indent; sql; uncache_sql; cache_sql; characterset_sql; column_parts; column_sql; pseudocolumn_sql; columnposition_sql; columnconstraint_sql; autoincrementcolumnconstraint_sql; compresscolumnconstraint_sql; generatedasidentitycolumnconstraint_sql; generatedasrowcolumnconstraint_sql; periodforsystemtimeconstraint_sql; notnullcolumnconstraint_sql; primarykeycolumnconstraint_sql; uniquecolumnconstraint_sql; inoutcolumnconstraint_sql; createable_sql; create_sql; sequenceproperties_sql; triggerproperties_sql; triggerreferencing_sql; triggerevent_sql; clone_sql; describe_sql; heredoc_sql; prepend_ctes; with_sql; cte_sql; tablealias_sql; bitstring_sql; hexstring_sql; bytestring_sql; unicodestring_sql; rawstring_sql; datatypeparam_sql; datatype_param_bound_limiter; directory_sql; delete_sql; drop_sql; set_operation; set_operations; fetch_sql; limitoptions_sql; filter_sql; hint_sql; indexparameters_sql; index_sql; identifier_sql; hex_sql; lowerhex_sql; inputoutputformat_sql; national_sql; partition_sql; properties_sql; root_properties; properties; with_properties; locate_properties; property_name; property_sql; uuidproperty_sql; likeproperty_sql; fallbackproperty_sql; journalproperty_sql; freespaceproperty_sql; checksumproperty_sql; mergeblockratioproperty_sql; moduleproperty_sql; datablocksizeproperty_sql; blockcompressionproperty_sql; isolatedloadingproperty_sql; partitionboundspec_sql; partitionedofproperty_sql; lockingproperty_sql; withdataproperty_sql; withsystemversioningproperty_sql; insert_sql; introducer_sql; kill_sql; pseudotype_sql; objectidentifier_sql; onconflict_sql; returning_sql; rowformatdelimitedproperty_sql; withtablehint_sql; indextablehint_sql; historicaldata_sql; table_parts; table_sql; tablefromrows_sql; tablesample_sql; pivot_sql; version_sql; tuple_sql; update_sql; values_sql; var_sql; into_sql; from_sql; groupingsets_sql; rollup_sql; rollupindex_sql; rollupproperty_sql; cube_sql; group_sql; having_sql; connect_sql; prior_sql; join_sql; lambda_sql; lateral_op; limit_sql; offset_sql; setitem_sql; set_sql; queryband_sql; pragma_sql; lock_sql; literal_sql; escape_str; loaddata_sql; null_sql; boolean_sql; booland_sql; boolor_sql; order_sql; withfill_sql; cluster_sql; distribute_sql; sort_sql; ordered_sql; matchrecognizemeasure_sql; matchrecognize_sql; query_modifiers; options_modifier; for_modifiers; queryoption_sql; offset_limit_modifiers; after_limit_modifiers; select_sql; schema_sql; schema_columns_sql; star_sql; parameter_sql; sessionparameter_sql; subquery_sql; qualify_sql; prewhere_sql; where_sql; window_sql; partition_by_sql; windowspec_sql; withingroup_sql; between_sql; bracket_offset_expressions; all_sql; any_sql; exists_sql; case_sql; constraint_sql; nextvaluefor_sql; extract_sql; trim_sql; convert_concat_args; concat_sql; concatws_sql; check_sql; foreignkey_sql; primarykey_sql; if_sql; jsonkeyvalue_sql; jsonpath_sql; json_path_part; formatjson_sql; formatphrase_sql; jsonarray_sql; jsonarrayagg_sql; jsoncolumndef_sql; jsonschema_sql; jsontable_sql; openjsoncolumndef_sql; openjson_sql; in_sql; in_unnest_op; return_sql; reference_sql; anonymous_sql; paren_sql; neg_sql; not_sql; alias_sql; pivotalias_sql; aliases_sql; atindex_sql; attimezone_sql; fromtimezone_sql; add_sql; and_sql; or_sql; xor_sql; connector_sql; bitwiseand_sql; bitwiseleftshift_sql; bitwisenot_sql; bitwiseor_sql; bitwiserightshift_sql; bitwisexor_sql; strtotime_sql; currentdate_sql; collate_sql; command_sql; comment_sql; mergetreettlaction_sql; mergetreettl_sql; transaction_sql; commit_sql; rollback_sql; altercolumn_sql; modifycolumn_sql; alterindex_sql; alterdiststyle_sql; altersortkey_sql; alterrename_sql; renamecolumn_sql; alter_sql; altersession_sql; add_column_sql; droppartition_sql; dropprimarykey_sql; addconstraint_sql; addpartition_sql; distinct_sql; havingmax_sql; intdiv_sql; dpipe_sql; div_sql; safedivide_sql; overlaps_sql; distance_sql; dot_sql; eq_sql; propertyeq_sql; escape_sql; glob_sql; gt_sql; gte_sql; is_sql; like_sql; ilike_sql; match_sql; similarto_sql; lt_sql; lte_sql; mod_sql; mul_sql; neq_sql; nullsafeeq_sql; nullsafeneq_sql; sub_sql; trycast_sql; jsoncast_sql; try_sql; log_sql; use_sql; binary; ceil_floor; function_fallback_sql; func; format_args; too_wide; format_time; expressions; op_expressions; naked_property; tag_sql; token_sql; userdefinedfunction_sql; joinhint_sql; kwarg_sql; when_sql; whens_sql; merge_sql; tochar_sql; tonumber_sql; dictproperty_sql; dictrange_sql; dictsubproperty_sql; duplicatekeyproperty_sql; uniquekeyproperty_sql; distributedbyproperty_sql; oncluster_sql; clusteredbyproperty_sql; anyvalue_sql; querytransform_sql; indexconstraintoption_sql; checkcolumnconstraint_sql; indexcolumnconstraint_sql; nvl2_sql; comprehension_sql; columnprefix_sql; opclass_sql; predict_sql; generateembedding_sql; generatetext_sql; generatetable_sql; generatebool_sql; generateint_sql; generatedouble_sql; mltranslate_sql; mlforecast_sql; aiforecast_sql; featuresattime_sql; vectorsearch_sql; forin_sql; refresh_sql; toarray_sql; tsordstotime_sql; tsordstotimestamp_sql; tsordstodatetime_sql; tsordstodate_sql; unixdate_sql; lastday_sql; dateadd_sql; arrayany_sql; struct_sql; partitionrange_sql; truncatetable_sql; convert_sql; copyparameter_sql; credentials_sql; copy_sql; semicolon_sql; datadeletionproperty_sql; maskingpolicycolumnconstraint_sql; gapfill_sql; scope_resolution; scoperesolution_sql; parsejson_sql; rand_sql; changes_sql; pad_sql; summarize_sql; explodinggenerateseries_sql; converttimezone_sql; json_sql; jsonvalue_sql; skipjsoncolumn_sql; conditionalinsert_sql; multitableinserts_sql; oncondition_sql; jsonextractquote_sql; jsonexists_sql; arrayagg_sql; slice_sql; apply_sql; grant_sql; revoke_sql; grantprivilege_sql; grantprincipal_sql; columns_sql; overlay_sql; todouble_sql; string_sql; median_sql; overflowtruncatebehavior_sql; unixseconds_sql; arraysize_sql; attach_sql; detach_sql; attachoption_sql; watermarkcolumnconstraint_sql; encodeproperty_sql; includeproperty_sql; xmlelement_sql; xmlkeyvalueoption_sql; partitionbyrangeproperty_sql; partitionbyrangepropertydynamic_sql; unpivotcolumns_sql; analyzesample_sql; analyzestatistics_sql; analyzehistogram_sql; analyzedelete_sql; analyzelistchainedrows_sql; analyzevalidate_sql; analyze_sql; xmltable_sql; xmlnamespace_sql; export_sql; declare_sql; declareitem_sql; recursivewithsearch_sql; parameterizedagg_sql; anonymousaggfunc_sql; combinedaggfunc_sql; combinedparameterizedagg_sql; show_sql; install_sql; get_put_sql; translatecharacters_sql; decodecase_sql; semanticview_sql; getextract_sql; datefromunixdate_sql; space_sql; buildproperty_sql; refreshtriggerproperty_sql; modelattribute_sql; directorystage_sql; uuid_sql; initcap_sql; localtime_sql; localtimestamp_sql; weekstart_sql; chr_sql; block_sql; storedprocedure_sql; ifblock_sql; whileblock_sql; execute_sql; executesql_sql; altermodifysqlsecurity_sql; usingproperty_sql; renameindex_sql
sqlglot.generators.postgres.PostgresGenerator: SELECT_KINDS; TRY_SUPPORTED; SUPPORTS_UESCAPE; AFTER_HAVING_MODIFIER_TRANSFORMS; SINGLE_STRING_INTERVAL; RENAME_TABLE_WITH_DB; JOIN_HINTS; TABLE_HINTS; PARAMETER_TOKEN; NAMED_PLACEHOLDER_TOKEN; TABLESAMPLE_SIZE_IS_ROWS; TABLESAMPLE_SEED_KEYWORD; SUPPORTS_SELECT_INTO; JSON_TYPE_REQUIRED_FOR_EXTRACTION; SUPPORTS_UNLOGGED_TABLES; LIKE_PROPERTY_INSIDE_SCHEMA; SUPPORTS_WINDOW_EXCLUDE; COPY_HAS_INTO_KEYWORD; ARRAY_SIZE_DIM_REQUIRED; INOUT_SEPARATOR; SUPPORTED_JSON_PATH_PARTS; lateral_sql; PROPERTIES_LOCATION; schemacommentproperty_sql; commentcolumnconstraint_sql; columndef_sql; bracket_sql; matchagainst_sql; computedcolumnconstraint_sql; isascii_sql; currentschema_sql; interval_sql; placeholder_sql