Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [money]

A data type to store monetary values

In SQL Server the data type MONEY uses 8 bytes to store a values in any currency in the numeric range from -922337203685477.5808 to 922337203685477.5807

In PostgreSQL the data type money also occupies 8 bytes but the allowed range is -92233720368547758.08 to +92233720368547758.07 by default (no other setting in lc_monetary). Same precision, but different scale - only two fractional digits.

14 questions
24
votes
2 answers

Storing prices in SQLite, what data-type to use?

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?
unixman83
  • 343
  • 1
  • 2
  • 6
11
votes
1 answer

PostgreSQL and MONEY data type for currency values

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…
Yanick Rochon
  • 1,651
  • 4
  • 20
  • 28
10
votes
3 answers

Efficiently comparing prices in different currencies

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…
Taai
  • 210
  • 2
  • 8
7
votes
3 answers

How to change the datatype of a column from integer to money?

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…
Gabriel
  • 71
  • 1
  • 1
  • 2
7
votes
1 answer

ERROR: invalid input syntax for type money

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…
Akash Kumar
  • 83
  • 2
  • 6
5
votes
2 answers

Is it possible to insert un-formatted data for money data type in PostgreSQL

By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $. How can I insert locale specific values? How can I insert values with my own digit grouping and decimal…
Ramesh Bathini
  • 61
  • 1
  • 6
4
votes
1 answer

Exchange rate table and conversion on the fly in query

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)…
Alexis
  • 289
  • 2
  • 6
  • 12
3
votes
4 answers

Handling currencies in a database

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…
Konrad
  • 219
  • 3
  • 12
2
votes
1 answer

How can I alter the type of a money-column to decimal in PostgreSQL?

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…
Jonas
  • 33,945
  • 27
  • 62
  • 64
0
votes
0 answers

Storing currency with variable floating point in SQLite

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…
MxLDevs
  • 359
  • 1
  • 5
  • 10
0
votes
1 answer

How to convert numeric to Polish currency

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…
Radoslaw
  • 1
  • 1
0
votes
1 answer

Default value format of a stored procedure parameter(of datatype : money)

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…
minijebu
  • 11
  • 2
0
votes
1 answer

What is the best data type to store prices upto 8 decimals + indicators in SQLite?

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…
PirateApp
  • 316
  • 4
  • 18
0
votes
1 answer

Handling differences between MS-SQL and PostgreSQL money datatypes

This is a follow-up/ to my previous question Is it possible to insert un-formatted data for money data type in PostgreSQL I could read money data by typecasting it into numeric SELECT '52093.89'::money::numeric Will it give inconsistent data,…
Ramesh Bathini
  • 61
  • 1
  • 6