sqlglot.dialects.redshift

 17class Redshift(Postgres):
 18    # https://docs.aws.amazon.com/redshift/latest/dg/r_names.html
 19    RESOLVES_IDENTIFIERS_AS_UPPERCASE = None
 20
 21    TIME_FORMAT = "'YYYY-MM-DD HH:MI:SS'"
 22    TIME_MAPPING = {
 23        **Postgres.TIME_MAPPING,
 24        "MON": "%b",
 25        "HH": "%H",
 26    }
 27
 28    class Parser(Postgres.Parser):
 29        FUNCTIONS = {
 30            **Postgres.Parser.FUNCTIONS,
 31            "DATEADD": lambda args: exp.DateAdd(
 32                this=exp.TsOrDsToDate(this=seq_get(args, 2)),
 33                expression=seq_get(args, 1),
 34                unit=seq_get(args, 0),
 35            ),
 36            "DATEDIFF": lambda args: exp.DateDiff(
 37                this=exp.TsOrDsToDate(this=seq_get(args, 2)),
 38                expression=exp.TsOrDsToDate(this=seq_get(args, 1)),
 39                unit=seq_get(args, 0),
 40            ),
 41            "NVL": exp.Coalesce.from_arg_list,
 42            "STRTOL": exp.FromBase.from_arg_list,
 43        }
 44
 45        def _parse_types(
 46            self, check_func: bool = False, schema: bool = False
 47        ) -> t.Optional[exp.Expression]:
 48            this = super()._parse_types(check_func=check_func, schema=schema)
 49
 50            if (
 51                isinstance(this, exp.DataType)
 52                and this.is_type("varchar")
 53                and this.expressions
 54                and this.expressions[0].this == exp.column("MAX")
 55            ):
 56                this.set("expressions", [exp.var("MAX")])
 57
 58            return this
 59
 60        def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]:
 61            to = self._parse_types()
 62            self._match(TokenType.COMMA)
 63            this = self._parse_bitwise()
 64            return self.expression(exp.TryCast, this=this, to=to)
 65
 66    class Tokenizer(Postgres.Tokenizer):
 67        BIT_STRINGS = []
 68        HEX_STRINGS = []
 69        STRING_ESCAPES = ["\\"]
 70
 71        KEYWORDS = {
 72            **Postgres.Tokenizer.KEYWORDS,
 73            "HLLSKETCH": TokenType.HLLSKETCH,
 74            "SUPER": TokenType.SUPER,
 75            "SYSDATE": TokenType.CURRENT_TIMESTAMP,
 76            "TIME": TokenType.TIMESTAMP,
 77            "TIMETZ": TokenType.TIMESTAMPTZ,
 78            "TOP": TokenType.TOP,
 79            "UNLOAD": TokenType.COMMAND,
 80            "VARBYTE": TokenType.VARBINARY,
 81        }
 82
 83        # Redshift allows # to appear as a table identifier prefix
 84        SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy()
 85        SINGLE_TOKENS.pop("#")
 86
 87    class Generator(Postgres.Generator):
 88        LOCKING_READS_SUPPORTED = False
 89        RENAME_TABLE_WITH_DB = False
 90        QUERY_HINTS = False
 91
 92        TYPE_MAPPING = {
 93            **Postgres.Generator.TYPE_MAPPING,
 94            exp.DataType.Type.BINARY: "VARBYTE",
 95            exp.DataType.Type.VARBINARY: "VARBYTE",
 96            exp.DataType.Type.INT: "INTEGER",
 97        }
 98
 99        PROPERTIES_LOCATION = {
100            **Postgres.Generator.PROPERTIES_LOCATION,
101            exp.LikeProperty: exp.Properties.Location.POST_WITH,
102        }
103
104        TRANSFORMS = {
105            **Postgres.Generator.TRANSFORMS,
106            exp.Concat: concat_to_dpipe_sql,
107            exp.CurrentTimestamp: lambda self, e: "SYSDATE",
108            exp.DateAdd: lambda self, e: self.func(
109                "DATEADD", exp.var(e.text("unit") or "day"), e.expression, e.this
110            ),
111            exp.DateDiff: lambda self, e: self.func(
112                "DATEDIFF", exp.var(e.text("unit") or "day"), e.expression, e.this
113            ),
114            exp.DistKeyProperty: lambda self, e: f"DISTKEY({e.name})",
115            exp.DistStyleProperty: lambda self, e: self.naked_property(e),
116            exp.FromBase: rename_func("STRTOL"),
117            exp.JSONExtract: _json_sql,
118            exp.JSONExtractScalar: _json_sql,
119            exp.SafeConcat: concat_to_dpipe_sql,
120            exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]),
121            exp.SortKeyProperty: lambda self, e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})",
122            exp.TsOrDsToDate: lambda self, e: self.sql(e.this),
123        }
124
125        # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots
126        TRANSFORMS.pop(exp.Pivot)
127
128        # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres)
129        TRANSFORMS.pop(exp.Pow)
130
131        RESERVED_KEYWORDS = {*Postgres.Generator.RESERVED_KEYWORDS, "snapshot", "type"}
132
133        def values_sql(self, expression: exp.Values) -> str:
134            """
135            Converts `VALUES...` expression into a series of unions.
136
137            Note: If you have a lot of unions then this will result in a large number of recursive statements to
138            evaluate the expression. You may need to increase `sys.setrecursionlimit` to run and it can also be
139            very slow.
140            """
141
142            # The VALUES clause is still valid in an `INSERT INTO ..` statement, for example
143            if not expression.find_ancestor(exp.From, exp.Join):
144                return super().values_sql(expression)
145
146            column_names = expression.alias and expression.args["alias"].columns
147
148            selects = []
149            rows = [tuple_exp.expressions for tuple_exp in expression.expressions]
150
151            for i, row in enumerate(rows):
152                if i == 0 and column_names:
153                    row = [
154                        exp.alias_(value, column_name)
155                        for value, column_name in zip(row, column_names)
156                    ]
157
158                selects.append(exp.Select(expressions=row))
159
160            subquery_expression: exp.Select | exp.Union = selects[0]
161            if len(selects) > 1:
162                for select in selects[1:]:
163                    subquery_expression = exp.union(subquery_expression, select, distinct=False)
164
165            return self.subquery_sql(subquery_expression.subquery(expression.alias))
166
167        def with_properties(self, properties: exp.Properties) -> str:
168            """Redshift doesn't have `WITH` as part of their with_properties so we remove it"""
169            return self.properties(properties, prefix=" ", suffix="")
170
171        def datatype_sql(self, expression: exp.DataType) -> str:
172            """
173            Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
174            VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
175            without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
176            `TEXT` to `VARCHAR`.
177            """
178            if expression.is_type("text"):
179                expression = expression.copy()
180                expression.set("this", exp.DataType.Type.VARCHAR)
181                precision = expression.args.get("expressions")
182
183                if not precision:
184                    expression.append("expressions", exp.var("MAX"))
185
186            return super().datatype_sql(expression)

28    class Parser(Postgres.Parser):
29        FUNCTIONS = {
30            **Postgres.Parser.FUNCTIONS,
31            "DATEADD": lambda args: exp.DateAdd(
32                this=exp.TsOrDsToDate(this=seq_get(args, 2)),
33                expression=seq_get(args, 1),
34                unit=seq_get(args, 0),
35            ),
36            "DATEDIFF": lambda args: exp.DateDiff(
37                this=exp.TsOrDsToDate(this=seq_get(args, 2)),
38                expression=exp.TsOrDsToDate(this=seq_get(args, 1)),
39                unit=seq_get(args, 0),
40            ),
41            "NVL": exp.Coalesce.from_arg_list,
42            "STRTOL": exp.FromBase.from_arg_list,
43        }
44
45        def _parse_types(
46            self, check_func: bool = False, schema: bool = False
47        ) -> t.Optional[exp.Expression]:
48            this = super()._parse_types(check_func=check_func, schema=schema)
49
50            if (
51                isinstance(this, exp.DataType)
52                and this.is_type("varchar")
53                and this.expressions
54                and this.expressions[0].this == exp.column("MAX")
55            ):
56                this.set("expressions", [exp.var("MAX")])
57
58            return this
59
60        def _parse_convert(self, strict: bool) -> t.Optional[exp.Expression]:
61            to = self._parse_types()
62            self._match(TokenType.COMMA)
63            this = self._parse_bitwise()
64            return self.expression(exp.TryCast, this=this, to=to)

66    class Tokenizer(Postgres.Tokenizer):
67        BIT_STRINGS = []
68        HEX_STRINGS = []
69        STRING_ESCAPES = ["\\"]
70
71        KEYWORDS = {
72            **Postgres.Tokenizer.KEYWORDS,
73            "HLLSKETCH": TokenType.HLLSKETCH,
74            "SUPER": TokenType.SUPER,
75            "SYSDATE": TokenType.CURRENT_TIMESTAMP,
76            "TIME": TokenType.TIMESTAMP,
77            "TIMETZ": TokenType.TIMESTAMPTZ,
78            "TOP": TokenType.TOP,
79            "UNLOAD": TokenType.COMMAND,
80            "VARBYTE": TokenType.VARBINARY,
81        }
82
83        # Redshift allows # to appear as a table identifier prefix
84        SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy()
85        SINGLE_TOKENS.pop("#")

 87    class Generator(Postgres.Generator):
 88        LOCKING_READS_SUPPORTED = False
 89        RENAME_TABLE_WITH_DB = False
 90        QUERY_HINTS = False
 91
 92        TYPE_MAPPING = {
 93            **Postgres.Generator.TYPE_MAPPING,
 94            exp.DataType.Type.BINARY: "VARBYTE",
 95            exp.DataType.Type.VARBINARY: "VARBYTE",
 96            exp.DataType.Type.INT: "INTEGER",
 97        }
 98
 99        PROPERTIES_LOCATION = {
100            **Postgres.Generator.PROPERTIES_LOCATION,
101            exp.LikeProperty: exp.Properties.Location.POST_WITH,
102        }
103
104        TRANSFORMS = {
105            **Postgres.Generator.TRANSFORMS,
106            exp.Concat: concat_to_dpipe_sql,
107            exp.CurrentTimestamp: lambda self, e: "SYSDATE",
108            exp.DateAdd: lambda self, e: self.func(
109                "DATEADD", exp.var(e.text("unit") or "day"), e.expression, e.this
110            ),
111            exp.DateDiff: lambda self, e: self.func(
112                "DATEDIFF", exp.var(e.text("unit") or "day"), e.expression, e.this
113            ),
114            exp.DistKeyProperty: lambda self, e: f"DISTKEY({e.name})",
115            exp.DistStyleProperty: lambda self, e: self.naked_property(e),
116            exp.FromBase: rename_func("STRTOL"),
117            exp.JSONExtract: _json_sql,
118            exp.JSONExtractScalar: _json_sql,
119            exp.SafeConcat: concat_to_dpipe_sql,
120            exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]),
121            exp.SortKeyProperty: lambda self, e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})",
122            exp.TsOrDsToDate: lambda self, e: self.sql(e.this),
123        }
124
125        # Postgres maps exp.Pivot to no_pivot_sql, but Redshift support pivots
126        TRANSFORMS.pop(exp.Pivot)
127
128        # Redshift uses the POW | POWER (expr1, expr2) syntax instead of expr1 ^ expr2 (postgres)
129        TRANSFORMS.pop(exp.Pow)
130
131        RESERVED_KEYWORDS = {*Postgres.Generator.RESERVED_KEYWORDS, "snapshot", "type"}
132
133        def values_sql(self, expression: exp.Values) -> str:
134            """
135            Converts `VALUES...` expression into a series of unions.
136
137            Note: If you have a lot of unions then this will result in a large number of recursive statements to
138            evaluate the expression. You may need to increase `sys.setrecursionlimit` to run and it can also be
139            very slow.
140            """
141
142            # The VALUES clause is still valid in an `INSERT INTO ..` statement, for example
143            if not expression.find_ancestor(exp.From, exp.Join):
144                return super().values_sql(expression)
145
146            column_names = expression.alias and expression.args["alias"].columns
147
148            selects = []
149            rows = [tuple_exp.expressions for tuple_exp in expression.expressions]
150
151            for i, row in enumerate(rows):
152                if i == 0 and column_names:
153                    row = [
154                        exp.alias_(value, column_name)
155                        for value, column_name in zip(row, column_names)
156                    ]
157
158                selects.append(exp.Select(expressions=row))
159
160            subquery_expression: exp.Select | exp.Union = selects[0]
161            if len(selects) > 1:
162                for select in selects[1:]:
163                    subquery_expression = exp.union(subquery_expression, select, distinct=False)
164
165            return self.subquery_sql(subquery_expression.subquery(expression.alias))
166
167        def with_properties(self, properties: exp.Properties) -> str:
168            """Redshift doesn't have `WITH` as part of their with_properties so we remove it"""
169            return self.properties(properties, prefix=" ", suffix="")
170
171        def datatype_sql(self, expression: exp.DataType) -> str:
172            """
173            Redshift converts the `TEXT` data type to `VARCHAR(255)` by default when people more generally mean
174            VARCHAR of max length which is `VARCHAR(max)` in Redshift. Therefore if we get a `TEXT` data type
175            without precision we convert it to `VARCHAR(max)` and if it does have precision then we just convert
176            `TEXT` to `VARCHAR`.
177            """
178            if expression.is_type("text"):
179                expression = expression.copy()
180                expression.set("this", exp.DataType.Type.VARCHAR)
181                precision = expression.args.get("expressions")
182
183                if not precision:
184                    expression.append("expressions", exp.var("MAX"))
185
186            return super().datatype_sql(expression)