This looks like a bug to me – I do not have 9.0 or 9.1 to test on yet, hopefully someone else can’t say if it is fixed in those versions or if we should raise it as a bug.Īside: your original will of course fail even with an explicit cast if there are any rows where price>=1000: alter table products alter column price type money using to_char(price, '999.99')::money ĮRROR: invalid input syntax for type money: " #. They are a part of Data type formatting functions. 99 in the format string, the TONUMBER () function will not parse the part after the decimal place. Discount AS money) AS Discount But I cannot figure out. The TOCHAR function in PostgreSQL is used to convert various data types like date or time, integer, floating-point to formatted strings, and also, they can be used to convert formatted strings to specific data types. Therefore, we can use it to format numbers as currency by passing. Note that while select to_char(78, '999.99')::money succeeds, the following fails: alter table products alter column price type money using to_char(price, '999.99') ĮRROR: column "price" cannot be cast to type moneyīut with an explicit cast it succeeds: alter table products alter column price type money using to_char(price, '999.99')::money In PostgreSQL strings can be converted to integer or double using CAST function or the :: annotation. PostgreSQL has a money data type that will typically be formatted in the locale currency when output: SELECT CAST(3145 as money) Result: PostgreSQL also has a TOCHAR() function that converts the value to a string and formats it based on the format string we supply. SELECT CAST23.7 AS varchar) AS int, CAST(23.7 AS int) AS decimal The following example converts string to int and int to decimal. As francs already implied, there is no default cast from integer to money (until 9.1), instead you need an intermediate cast to text: select 78:: integer::money The following example casts a string to int and int to decimal.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |