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:

Patch:
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index e0f3c1b2..57984784 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -687,6 +687,14 @@ class Postgres(Dialect):
                     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 971d81b3..4a70859e 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -320,6 +320,7 @@ class TestRedshift(Validator):
         )
 
     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")