Repository: tobymao/sqlglot

Issue:
Invalid syntax for Redshift:  AS REAL(8)
I'm building a parse tree that includes selecting from a derived table that has its own SQL, which I then just include natively in the parse tree.

```
SELECT
    b.proud_to_order_by_name::float(8)
```

When SQLGlot then generates the SQL for Redshift, it's doing this:

```
SELECT
    CAST(b.proud_to_order_by_name AS REAL(8)),
```

But Redshift throws an exception:  `com.amazon.redshift.util.RedshiftException: ERROR: syntax error at or near "(" in context "proud_to_order_by_name AS REAL("`

It seems to be happy when I run the SQL manually with just `AS REAL`.

Let me know, thanks.

Discussion:
@georgesittas: @VaggelisD looks like `REAL` and `DOUBLE PRECISION` can't have a precision specified for them.@RedTailedHawk: Thanks for fixing.  Should I look for it in the next release on pypi.org?@georgesittas: Yep@RedTailedHawk: Looks like it's not fixed in `sqlglot 26.0.1`.  Can you please confirm?@RedTailedHawk: Looks like it's not fixed in `26.2.1` either.  Any plans to fix?@georgesittas: Hey @RedTailedHawk, can you share the exact code you're running?@RedTailedHawk: ```
from sqlglot import parse_one

dialect = "redshift"

sql = """
SELECT proud_to_order_by_name::float(8)
FROM test 
"""

parse_tree = parse_one(sql, dialect=dialect)

# print(repr(parse_tree))

sql = parse_tree.sql(dialect, pretty = True, pad = 4)

print(sql)
```

Prints:

```
SELECT
    CAST(proud_to_order_by_name AS DOUBLE PRECISION(8))
FROM test
```@georgesittas: I can't reproduce this on latest main:

```python
>>> from sqlglot import parse_one
>>>
>>> dialect = "redshift"
>>>
>>> sql = """
... SELECT proud_to_order_by_name::float(8)
... FROM test
... """
>>>
>>> parse_tree = parse_one(sql, dialect=dialect)
>>>
>>> # print(repr(parse_tree))
>>>
>>> sql = parse_tree.sql(dialect, pretty = True, pad = 4)
>>>
>>> print(sql)
SELECT
    CAST(proud_to_order_by_name AS FLOAT(8))
FROM test
```@RedTailedHawk: So it was fixed on `main`?  If so, great!  I'll wait for the next release.@georgesittas: This should've been fixed for quite some time now. Maybe you're using an outdated installation, make sure to clear caches etc.@RedTailedHawk: It's working, thank you.

Solution patch:
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index e0f3c1b2bd..57984784bb 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -687,6 +687,14 @@ def datatype_sql(self, expression: exp.DataType) -> str:
                     values = self.expressions(expression, key="values", flat=True)
                     return f"{self.expressions(expression, flat=True)}[{values}]"
                 return "ARRAY"
+
+            if (
+                expression.is_type(exp.DataType.Type.DOUBLE, exp.DataType.Type.FLOAT)
+                and expression.expressions
+            ):
+                # Postgres doesn't support precision for REAL and DOUBLE PRECISION types
+                return f"FLOAT({self.expressions(expression, flat=True)})"
+
             return super().datatype_sql(expression)
 
         def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:


Test patch:
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 971d81b303..4a70859e42 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -320,6 +320,7 @@ def test_redshift(self):
         )
 
     def test_identity(self):
+        self.validate_identity("SELECT CAST(value AS FLOAT(8))")
         self.validate_identity("1 div", "1 AS div")
         self.validate_identity("LISTAGG(DISTINCT foo, ', ')")
         self.validate_identity("CREATE MATERIALIZED VIEW orders AUTO REFRESH YES AS SELECT 1")