The function code itself is responsible for what it returns. Timestamps in PostgreSQL. It is recommended against using floating point numbers to handle money due to the potential for rounding errors. We can understand the concept of precision and scale by seeing in the following example: Suppose we have the number 2356.78. Apache Derby and PostgreSQL - Criteria selectCase with Enu Curiosily the "NULL to SqlType" not works, "ERROR: cannot cast jsonb null to type integer". boolean→boolean, number→numeric, number→int, number→bigint; number→flloat, number→double. Postgresql cast double precision to numeric. For example, what wold be faster (?) CAST(number AS double precision) or alternatively number::double precision: If a column contains money data you should keep in mind that floating point numbers should not be used to handle money due to the potential for rounding errors. SELECT cast(123456.78 as money); Result: $123,456.78 Convert from a Floating Point Number. I would like to have the data in my table with scale and precision, but my views to be cast to numeric without any scale or precision. RETURNS TABLE(m numeric(20,10), n numeric(20,10)) does not enforce the data types of the returned columns; it's simply a declaration, metadata intended to inform the callers what the function should return. In case of processor memory, the double precision types can occupy up to 64 bit of memory. The to_date function in PostgreSQL is used to converting strings into CAST( number AS double precision) or alternatively number::double Can't convert to Date – BWhite Aug 30 '19 at 23:30 what version of postgres? tham "binary JSONb → string → binary SQL"? Text and characters. Double precision expression takes more decimal points when compared to float data types. select cast(109.652 as decimal(4,1)); numeric ----- 109.7 In this example, the PRICEPAID column (a DECIMAL(8,2) column) in the SALES table is converted to a DECIMAL(38,2) column and the values are multiplied by 100000000000000000000. Note: In PostgreSQL, the Numeric data type can have a value of up to 131,072 digits before the decimal point of 16,383 digits after the decimal point. Stephan Szabo It's more complicated than that (and postgres does some of this but not all), for example the cast text->float8->numeric potentially loses precision and should probably not be an automatic cast for that reason. PostgreSQL provides the DOUBLE PRECISION data type for this kind of numeric data– the data type offers … When converting from double precision, it is quite similar to rounding off the expression. There may be a need for documentation on this. However, when I cast a numeric(16,4) to a ::numeric it doesn't cast it. In this number, the precision is 6, and the scale is 2.. as you can see it worked for me (click the "here" in the answer) that s a live demo. The declaration cannot not require anything, it's a "promise" that is to be fulfilled by the function implementation. When you need to store numeric values with a large number of decimal digits, you need to make sure you utilize the correct data type for the task. 2) precision The precision argument is an integer that indicates the number of decimal places. Why not optimized for NULL? SELECT '123456.78'::float8::numeric::money; Result: $123,456.78 Numeric plain only shows numbers after the decimal point that are being used. There are two ways to typecast in Postgres: You either do it the SQL standard way: select cast(3.141593 as bigint); or you could use the Postgres-specific cast operator: :: select (3.141593 :: bigint); You might also want to consider the various rounding functions. String to numeric. I'm using 8.2.4 Numeric with scale precision always shows the trailing zeros. However, if you must do it, you can convert to numeric first, then to money. It is safe for money values to cast to and from the numeric type (used for arbitrary precision, as shown above), so it is recommended to always use numeric as an intermediary before performing converting to other types.