8/14/2023 0 Comments Postgresql cast integer to moneyThis 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: " #. 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 The short answer is that you'll need two columns. razpeitia at 5:10 For anyone working with multiple currencies and caring about storing currency codes in addition to the amounts, you may want to see Currency modeling in database (SO) and ISO 4217 (Wikipedia). The numeric->money cast does handle it though, so theres at least some precedent for checking. 13 If you have read the whole thread, Numeric is the way to go. It might be better > to leave it be, and plan to fix them all at once in a consistent style. Perhaps theyre integer, perhaps theyre numeric, perhaps youre using. Yet: - Executing query: select 123.45::money ERROR: cannot cast type numeric to money SQL. Both are equivalent and you can choose whichever you like. In the meantime, Im not sure whether the newly added functions should > be held to a higher standard than the existing ones. In your venerable orders table, youre almost certainly storing prices as numbers. Output is generally in the latter form but depends on the locale. 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 SELECT cast(123456.78 as money) Result: Convert from a Floating Point Number. Pratibha Sarin July 7th, 2022 PostgreSQL is a powerful open-source object-relational database system. In PostgreSQL strings can be converted to integer or double using CAST function or the :: annotation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |