Round of double not transpiled correctly for postgres

[blecourt-private]

Summary

• read: duckdb
• write: postgres, redshift, risingwave, materialize

In postgres and related dialects the following syntax for the ROUND function is valid:

round(numeric, integer)

and the following is not:

round(double precision, integer)

(On a side note: round(double precision) is allowed, without specifying the number of decimals.)

Sqlglot doesn't handle this correctly.

Reproducible code

from sqlglot import transpile
query="select round(cast(amount as double precision), 3)"
transpile(query, read="duckdb", write="postgres")

The result is ['SELECT ROUND(CAST(amount AS DOUBLE PRECISION), 3)']. (And similar if the write dialect is redshift or risingwave).

I have verified that SELECT ROUND(CAST(amount AS DOUBLE PRECISION), 3) throws an error in Postgres and RisingWave. I have not verified this for Redshift and Materialize (apart from consulting the docs for these dialects).

Docs

https://www.postgresql.org/docs/current/functions-math.html
https://docs.aws.amazon.com/redshift/latest/dg/r_ROUND.html
https://docs.risingwave.com/sql/functions/mathematical#mathematical-functions-and-operators
https://materialize.com/docs/sql/functions/#numbers-functions

Bonus info

This in turn has implications for sqlmesh table_diff. I have tables with a column amount of type DOUBLE PRECISION and this causes table_diff to throw the following error:

Schema Diff Between '<table a>' and '<table b>':
└── Schemas match
Traceback (most recent call last):

psycopg2.errors.InternalError_: Failed to run the query

Caused by these errors (recent errors listed first):
  1: Failed to bind expression: CASE WHEN ROUND("s"."amount", 3) = ROUND("t"."amount", 3) THEN 1 WHEN ("s"."amount" IS NULL) AND ("t"."amount" IS NULL) THEN 1 WHEN ("s"."amount" IS NULL) OR ("t"."amount" IS NULL) THEN 0 ELSE 0 END
  2: function round_digit(double precision, integer) does not exist

I am using a risingwave engine but because of the way sqlglot seems to transpile this expression to postgres I am expecting the same problem with postgres and other related dialects.