In PostgreSQL I would like to achieve something similar to mysql query: select price, format(price,2,'pl_PL') as pricePL from table *************************** 1. row *************************** price: 0.010000 pricePL: 0,01 In PostgreSQL I'm storing price as numeric(10,6)

This is a follow-up/ to my previous question https://dba.stackexchange.com/questions/183964/is-it-possible-to-insert-un-formatted-data-for-money-data-type-in-postgresql/184155#184155 1. I could read money data by typecasting it into numeric SELECT '52093.89'::money::numeric Will it give inconsistent data, rounded etc.. ? 2. As per ISO standard, the money datatype size is (19,4) but why PostgreSQL returns size as 2147483647? Is this size locale dependent? 3. Is it a good idea to use the money datatype to store/retrieve locale specific values? If not, when is the money datatype best suitable? I have to use the money datatype for certain cases. My worry is will I get any problems if I treat money datatype values as decimal values? I don't bother about locale specific values such as symbol, digit, and decimal grouping etc.. Can I store money datatype values as per lc_monetary settings and reading by typecasting it to numeric?

With my query: SELECT (CAST(0.00 AS MONEY)) I am expecting a result : 0.00 But it shows me the result: 0,00(with comma) I checked this query ,when i found that,the CREATE PROCEDURE script displays the default value as [money] = 0,0000, for one of the input parameters of a procedure. How can i make the CREATE PROCEDURE display it with '.' instead of ','?

How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies? Let's say I have Product table, this product has a price, but what currency should the price be in? Should it always be the same or should I also have Currency column with currency code or CurrencyID pointing to specific currency? Should I set currency per product or globally per database? What is your experience with that?

I am developing a trading system which stores data in SQLite, consisting of OHLC and volume. I would also like to store some indicators like RSI etc I read the other answer which indicated integers as the data type. In my case I am looking at 8 digit precision which would slow down the calculations if I were to multiply every value by 1e8 Any recommendations are appreciated!

By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $. 1. How can I insert locale specific values? 2. How can I insert values with my own digit grouping and decimal grouping formats? 3. How can I read unformatted data? 4. Why is PostgreSQL's money data type different than MS SQL's money data type?

While importing I get this error: > pg_restore: [archiver (db)] COPY failed for table "transaction_details": > ERROR: invalid input syntax for type money: "$0.00" restore completed, but the transaction_details table is empty. This is Heroku's PostgreSQL Dump database. PostgreSQL version on Heroku is 9.3.15 and the same on my system The command I used: pg_restore --verbose --clean --no-acl --no-owner -h localhost -U root -d database 577b86e3-8d96-4410-9b36-dd0f78cfe32f Could anyone suggest a solution?

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items. I'm working with Java and my first thought was that I would use some kind of API to pull the rates and post process (loop) after I get the the items from the DB. But I'd rather not have to unnecessarily loop if I can directly get the right price form the query. So I think I'm going to store all the exchanges rate in the DB and have my query do the conversation job here is my first draft: CREATE TABLE currency ( code VARCHAR(3) NOT NULL, symbol VARCHAR(3) NOT NULL, display_name VARCHAR(4) NOT NULL, PRIMARY KEY (code), UNIQUE KEY uq_ccode (code) ); CREATE TABLE exchange_rate ( currency_code_from VARCHAR(3) NOT NULL, currency_code_to VARCHAR(3) NOT NULL, rate numeric(15,4) NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (currency_code_from, currency_code_to), FOREIGN KEY (currency_code_from) REFERENCES currency(code) ON DELETE CASCADE, FOREIGN KEY (currency_code_to) REFERENCES currency(code) ON DELETE CASCADE ); I would then write a query like: select price as originalPrice, (price * rate) as TargetPrice FROM ..... JOIN exchange_rate er ON er.currency_code_from=(the currency of the item) AND er.currency_code_to=(the user currency)...... Note that on the above tables I would have to have every single combination GBPEUR EURGBP GBPGBP (well i guess i can probably avoid this one if a slighty amend the query to detect this scenario) Plus.. I would have to have some kind of cron job to daily update this table. Now I'm far to be a banker nor a SQL genius so I would like some input from people that have already written such tables/query and that have expertise in this kind of stuff Am I fare off with my design? Would I be better off post processing the results and do the conversation in java with rates coming from an API?

For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having values like $9.94 (string) being returned from the database, instead of 9.94 (numeric). Having to manually manipulate the field value by removing the dollar sign is silly; is there a way to have a MONEY column without a currency sign? If not, what's the best data type replacement to handle currency values?

I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is *unacceptable* storage for prices. Is there a data-type besides TEXT that I can use to store prices numerically so they sort correctly?

We are working with currency and handle currencies from various countries. As a result, we may work with currencies that require no decimal values, two decimal places, three, sometimes four. We also store taxes that are derived from individual items, and we usually store them with higher precision than the currency specifies (eg: taxes for Canadian dollars would be stored to 4 decimal places) What would be an appropriate way to store these values? I have read that many people store them as integers in the lowest denomination (eg: dollars would be stored in cents), but the 4-decimal taxes would not work out very well.

I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the product price is 200USD and, if the user searches the products that costs 100EUR - 200EUR, he still may find it. How to make it fast and effective? Here is what I have done until now. I store the

,

code

and the

that is the price in Euros (EUR) that is the default currency. CREATE TABLE "products" ( "id" serial, "price" numeric NOT NULL, "currency" char(3), "calculated_price" numeric NOT NULL, CONSTRAINT "products_id_pkey" PRIMARY KEY ("id") ); CREATE TABLE "currencies" ( "id" char(3) NOT NULL, "modified" timestamp NOT NULL, "is_default" boolean NOT NULL DEFAULT 'f', "value" numeric NOT NULL, -- ratio additional to the default currency CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id") ); INSERT INTO "currencies" (id, modified, is_default, value) VALUES ('EUR', '2012-05-17 11:38:45', 't', 1.0), ('USD', '2012-05-17 11:38:45', 'f', '1.2724'), ('GBP', '2012-05-17 11:38:45', 'f', '0.8005'); INSERT INTO "products" (price, currency, calculated_price) SELECT 200.0 AS price, 'USD' AS currency, (200.0 / value) AS calculated_price FROM "currencies" WHERE id = 'USD'; If user is searching with other currency, let's say USD, we calculate the price in EUR and search the

column. SELECT * FROM "products" WHERE calculated_price > 100.0 AND calculated_price < 200.0; In this way we can compare prices very fast, because we don't need to calculate the actual price for every row, because it's calculated once. Bad thing is that at least every day we have to re-calculate the

for all rows, because the currency rates has been changed. Is there a better way to do deal with this? Isn't there any other clever solution? Maybe some mathematical formula? I have an idea that the

is a ratio against some variable

and, when the currency changes, we update only that variable

, not the

, so we even don't need to update anything (rows)... Maybe some mathematician can solve it like this?

I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error: >cannot cast type MyColumn to money I have tried these two statements, but just haven't found any real example on how to really do it. ALTER TABLE products ALTER COLUMN price TYPE money and: ALTER TABLE products ALTER COLUMN price TYPE money USING to_char(price, '999.99') How can you change the datatype of a PostgreSQL column from integer to money?

I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead. How can I do it? I tried with: alter table my_table alter column vat type decimal; But I get this error: ERROR: column "vat" cannot be cast to type numeric Any suggestions?