postgresql
Postgresql showing only one decimal, Grafana showing 2
I don't understand how this is possible but for some reason, the bid/ask columnsonly show one decimal (should show 2):
arb_finder=# select * from order_book where exchange = 'Coinbase' and currency_1 = 'BTC' and timestamp >= timestamp '2024-07-23 10:36:36' and timestamp < timestamp '2024-07-23 10:36:40';
id | timestamp | currency_1 | currency_2 | bid_q | bid | ask | ask_q | exchange
---------+----------------------------+------------+------------+-----------+---------+---------+------------+----------
1407052 | 2024-07-23 10:36:36.038745 | BTC | USD | 0.102793 | 66957.8 | 66957.8 | 0.00105211 | Coinbase
1407053 | 2024-07-23 10:36:36.0886 | BTC | USD | 0.08678 | 66957.8 | 66960.2 | 0.00105212 | Coinbase
1407054 | 2024-07-23 10:36:36.138261 | BTC | USD | 0.08678 | 66957.8 | 66957.8 | 0.0342719 | Coinbase
1407055 | 2024-07-23 10:36:36.23883 | BTC | USD | 0.187353 | 66957.8 | 66957.8 | 0.0342719 | Coinbase
1407056 | 2024-07-23 10:36:36.288232 | BTC | USD | 0.406555 | 66957.8 | 66957.8 | 0.0343101 | Coinbase
1407057 | 2024-07-23 10:36:36.343896 | BTC | USD | 0.380639 | 66957.8 | 66957.8 | 0.0343101 | Coinbase
1407058 | 2024-07-23 10:36:36.535402 | BTC | USD | 0.380639 | 66957.8 | 66957.8 | 0.0343118 | Coinbase
1407059 | 2024-07-23 10:36:36.740284 | BTC | USD | 0.161437 | 66957.8 | 66957.8 | 0.0353636 | Coinbase
1407060 | 2024-07-23 10:36:36.972845 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.0353636 | Coinbase
1407061 | 2024-07-23 10:36:37.035819 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.0343118 | Coinbase
1407062 | 2024-07-23 10:36:37.334264 | BTC | USD | 0.370875 | 66957.8 | 66957.8 | 0.0240962 | Coinbase
1407063 | 2024-07-23 10:36:37.432583 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.0240962 | Coinbase
1407064 | 2024-07-23 10:36:37.492267 | BTC | USD | 0.370595 | 66957.8 | 66957.8 | 0.0240962 | Coinbase
1407065 | 2024-07-23 10:36:37.539213 | BTC | USD | 0.370595 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407066 | 2024-07-23 10:36:37.592276 | BTC | USD | 0.162024 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407067 | 2024-07-23 10:36:37.688685 | BTC | USD | 0.0766505 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407068 | 2024-07-23 10:36:37.790094 | BTC | USD | 0.0752607 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407069 | 2024-07-23 10:36:37.989782 | BTC | USD | 0.115134 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407070 | 2024-07-23 10:36:38.189248 | BTC | USD | 0.32433 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407071 | 2024-07-23 10:36:38.223394 | BTC | USD | 0.115134 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407072 | 2024-07-23 10:36:38.43737 | BTC | USD | 0.116524 | 66957.8 | 66957.8 | 0.101096 | Coinbase
1407073 | 2024-07-23 10:36:38.488503 | BTC | USD | 0.356598 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407074 | 2024-07-23 10:36:38.527654 | BTC | USD | 0.336855 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407075 | 2024-07-23 10:36:38.677178 | BTC | USD | 0.335465 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407076 | 2024-07-23 10:36:38.885509 | BTC | USD | 0.336855 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407077 | 2024-07-23 10:36:39.091913 | BTC | USD | 0.127504 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407078 | 2024-07-23 10:36:39.238957 | BTC | USD | 0.148094 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407079 | 2024-07-23 10:36:39.29049 | BTC | USD | 0.409813 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407080 | 2024-07-23 10:36:39.334706 | BTC | USD | 0.201914 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407081 | 2024-07-23 10:36:39.386161 | BTC | USD | 0.412944 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407082 | 2024-07-23 10:36:39.4421 | BTC | USD | 0.412944 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407083 | 2024-07-23 10:36:39.48631 | BTC | USD | 0.25149 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407084 | 2024-07-23 10:36:39.541874 | BTC | USD | 0.2501 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407085 | 2024-07-23 10:36:39.635424 | BTC | USD | 0.25149 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407086 | 2024-07-23 10:36:39.690689 | BTC | USD | 0.2501 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407087 | 2024-07-23 10:36:39.838419 | BTC | USD | 0.0404599 | 66957.8 | 66957.8 | 0.0990962 | Coinbase
1407088 | 2024-07-23 10:36:39.927487 | BTC | USD | 0.0404599 | 66957.8 | 66957.8 | 0.100148 | Coinbase
(37 rows)
I think the data stored is actually with a greater precision as I can see 2 decimals in the data pulled from Grafana:
(Grafana displays in BST, while the raw data is in UTC, hence the 1h difference)
What's even more confusing is that when importing the data in pandas, it also only shows with 1 decimal:
# Define your SQL query
query = """
SELECT
timestamp,
bid,
ask
FROM
order_book
WHERE
exchange = 'Coinbase' AND
currency_1 = 'BTC' AND
timestamp >= timestamp '2024-07-23 10:36:36' AND
timestamp < timestamp '2024-07-23 10:36:40'
ORDER BY timestamp
"""
df = pd.read_sql_query(query, engine)
print(df)
timestamp bid ask
0 2024-07-23 10:36:36.038745 66957.8 66957.8
1 2024-07-23 10:36:36.088600 66957.8 66960.2
2 2024-07-23 10:36:36.138261 66957.8 66957.8
3 2024-07-23 10:36:36.238830 66957.8 66957.8
4 2024-07-23 10:36:36.288232 66957.8 66957.8
5 2024-07-23 10:36:36.343896 66957.8 66957.8
6 2024-07-23 10:36:36.535402 66957.8 66957.8
7 2024-07-23 10:36:36.740284 66957.8 66957.8
8 2024-07-23 10:36:36.972845 66957.8 66957.8
9 2024-07-23 10:36:37.035819 66957.8 66957.8
10 2024-07-23 10:36:37.334264 66957.8 66957.8
11 2024-07-23 10:36:37.432583 66957.8 66957.8
12 2024-07-23 10:36:37.492267 66957.8 66957.8
13 2024-07-23 10:36:37.539213 66957.8 66957.8
14 2024-07-23 10:36:37.592276 66957.8 66957.8
15 2024-07-23 10:36:37.688685 66957.8 66957.8
16 2024-07-23 10:36:37.790094 66957.8 66957.8
17 2024-07-23 10:36:37.989782 66957.8 66957.8
18 2024-07-23 10:36:38.189248 66957.8 66957.8
19 2024-07-23 10:36:38.223394 66957.8 66957.8
20 2024-07-23 10:36:38.437370 66957.8 66957.8
21 2024-07-23 10:36:38.488503 66957.8 66957.8
22 2024-07-23 10:36:38.527654 66957.8 66957.8
[...]
What am I missing here?
Here's the structure of the order_book table:
arb_finder=# \d order_book
Table "public.order_book"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+----------------------------------------
id | bigint | | not null | nextval('order_book_id_seq'::regclass)
timestamp | timestamp without time zone | | | CURRENT_TIMESTAMP
currency_1 | text | | not null |
currency_2 | text | | not null |
bid_q | real | | not null |
bid | real | | not null |
ask | real | | not null |
ask_q | real | | not null |
exchange | text | | not null |
Indexes:
"order_book_pkey" PRIMARY KEY, btree (id)
Edit: I've just tried to insert a test entry with 2 decimals to rule out an issue with the actual data inserted and it still displays with only one decimal:
arb_finder=# insert into order_book (timestamp, currency_1, currency_2, bid_q, bid, ask, ask_q, exchange) values (now(), 'TEST', 'USD', 0.102793, 66957.82, 66957.83, 0.00105211, 'Coinbase');
arb_finder=# select * from order_book where currency_1 = 'TEST';
id | timestamp | currency_1 | currency_2 | bid_q | bid | ask | ask_q | exchange
---------+----------------------------+------------+------------+----------+---------+---------+------------+----------
1462509 | 2024-07-23 11:57:19.658942 | TEST | USD | 0.102793 | 66957.8 | 66957.8 | 0.00105211 | Coinbase
(1 row)
I then tried with a smaller number and it worked:
arb_finder=# insert into order_book (timestamp, currency_1, currency_2, bid_q, bid, ask, ask_q, exchange) values (now(), 'TEST', 'USD', 0.102793, 57.82, 66957.83, 0.00105211, 'Coinbase');
INSERT 0 1
arb_finder=# select * from order_book where currency_1 = 'TEST'; id | timestamp | currency_1 | currency_2 | bid_q | bid | ask | ask_q | exchange
---------+----------------------------+------------+------------+----------+---------+---------+------------+----------
1462509 | 2024-07-23 11:57:19.658942 | TEST | USD | 0.102793 | 66957.8 | 66957.8 | 0.00105211 | Coinbase
1464557 | 2024-07-23 11:58:11.950465 | TEST | USD | 0.102793 | 57.82 | 66957.8 | 0.00105211 | Coinbase
(2 rows)
Is it that I should be using a different data type (eg double precision instead of real)? But if that's the case, how come Grafana seems to be pulling 2 decimal precision?
Bid and Ask are of data type real. The PostgreSQL version is 11.22 (Raspbian 11.22-0+deb10u2). And SHOW extra_float_digits; returns 0. But Bid_q and Ask_q are also real and show 6 decimals.
Solution
The answer is in the lacking precision of floating point values. When you convert numbers between the decimal system and the binary representation used for floating point numbers, the result is usually not exact. For example, the binary representation of 0.1 has infinitely many decimal places. PostgreSQL tries to show only as many digits as can be guaranteed to be loss-free during this conversion. One of the reasons for that is that the displayed numbers should stay the same if you pg_dump and restore a database.
For that reason, PostgreSQL has a parameter extra_float_digits that controls how many digits are shown. The default value of 0 guarantees the stability of numbers as described above. Note that the implementation and meaning of this parameter changed in v12, the default value is now 1, but the general idea is the same.
It seems like the Grafana interface is using a higher value for extra_float_digits than the default setting you are using in psql.
Note that the number of digits that are shown is not the same for each number; it depends on the value.
I am not certain why Grafana does what it does, but my guess is that it uses the default setting for extra_float_digits from PostgreSQL v12 on. So perhaps all you have to do to make the difference disappear is to upgrade to a supported PostgreSQL version.
- Difference between text and varchar (character varying)
- PostgreSQL: Clone rows but replace one column
- Insert array of composite types into column of type array of composite type
- Heroku postgresql queries not working on Heroku, but work locally
- casting (converting) integer into interval in PostgreSQL
- Substring before first uppecase word excluding first word
- Restore a postgres backup file using the command line?
- Trying to get the next value in sequence from a postgres DB, with django
- Problem with Leetcode SQL task - Game Play Analysis IV
- Only one of two UPDATEs in CTEs is executed?
- Optional filter on query with sqlc w/ Postgres in Golang
- Function returning a table for given table and column names
- Type error while looping through array in PL/pgSQL
- Given time/interval to calculate open/high/low/close value in each grouped data
- Maximum characters in labels (table names, columns etc)
- Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
- Pros and drawbacks of storing files as bytestrings versus blobs in Postgres
- Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB
- How can I change a PostgreSQL user password?
- How to convert a postgres database to SQLite?
- SparkSQL JDBC (PySpark) to Postgres - Creating Tables and Using CTEs
- Check if database exists in PostgreSQL using shell
- postgres installation the database cluster initialization failed ( Postgresql Version 9.4.4 )
- Incorrect timezone with casting datetime string (earlier than 1948 year) to timestamptz or timetz
- How to pass the value of a req.body.name from post route in app.js to an API endpoint in a module.exports js file
- How to improve or speed up Postgres query with pg_trgm?
- Column doesn't exist when using WITH statement PostgreSQL
- NpgsqlException while connecting to Postgres: ERROR: 3D000: odyssey: c16b9035a1f78: route for 'template1.xxx' is not found
- pg Admin 4 - password for "postgres" user when trying to connect to PostgreSQL 13 server
- Issue Migrating SonarQube Database from aws Internal PostgreSQL to AWS RDS PostgreSQL