`CREATE TABLE pg.t AS …` creates HUGEINT columns as `double precision`, so `sum(bigint)` results above 2^53 are rounded (same problem as #246 for UBIGINT)
Maintainers usually reply within 1 day
Nobody has claimed this yet.
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 62/100
Research direction
Start in src/postgres_utils.cpp at PostgresUtils::ToPostgresType() around line 476, then review the existing UBIGINT mapping mentioned in the issue and its fix in #256. Add or run tests for PostgreSQL table creation from HUGEINT values, including sums above 2^53 and values at the HUGEINT limits. Done means created PostgreSQL columns use an exact numeric type and preserve those values.
Written by the indexing model from the issue text.
Description
What happens?
PostgresUtils::ToPostgresType() (src/postgres_utils.cpp:476) maps HUGEINT to DOUBLE. Every PostgreSQL table
the extension creates with a HUGEINT column therefore gets a double precision column, and every value above 2^53
(about 9·10^15) is stored rounded. This covers CREATE TABLE p.t AS …, CREATE TABLE p.t (c HUGEINT) and
COPY FROM DATABASE … TO p. There is no error or warning.
In DuckDB, sum() of a BIGINT column returns HUGEINT. So pushing an aggregate to PostgreSQL loses precision even
though the user never wrote HUGEINT. Row totals of cents, nanosecond sums and ID sums are typical cases.
The same problem for UBIGINT was reported in #246 and fixed by #256, which maps UBIGINT to DECIMAL(20,0). HUGEINT
kept the double mapping. numeric(39,0) (or plain numeric) would hold every HUGEINT value exactly. The sqlite
extension maps HUGEINT to VARCHAR and is exact.
Even inserting into an existing numeric(39,0) column rounds, because the extension reads numeric wider than 38 digits
as DOUBLE. That is the known read mapping (#51, pg_numeric_as_varchar), and I am not reporting it here.
To Reproduce
ATTACH 'host=127.0.0.1 port=5432 user=postgres dbname=postgres' AS p (TYPE postgres);
CREATE TABLE sales AS SELECT * FROM (VALUES (1, 9007199254740993::BIGINT), (2, 1::BIGINT), (2, 2::BIGINT)) v(store, cents);
CREATE TABLE p.totals AS SELECT store, sum(cents) AS total FROM sales GROUP BY store;
SELECT store, sum(cents) FROM sales GROUP BY store ORDER BY store; -- 1 | 9007199254740993
CREATE TABLE ledger(id INT, amount HUGEINT);
INSERT INTO ledger VALUES (1, 12345678901234567890123), (2, -170141183460469231731687303715884105728);
CREATE TABLE p.ledger AS FROM ledger;
In PostgreSQL:
postgres=# SELECT store, pg_typeof(total), total FROM totals ORDER BY store;
store | pg_typeof | total
-------+------------------+-----------------------
1 | double precision | 9.007199254740992e+15 <-- DuckDB computed 9007199254740993
2 | double precision | 3
postgres=# SELECT id, pg_typeof(amount), amount FROM ledger ORDER BY id;
id | pg_typeof | amount
----+------------------+-------------------------
1 | double precision | 1.2345678901234568e+22 <-- was 12345678901234567890123
2 | double precision | -1.7014118346046923e+38 <-- was -170141183460469231731687303715884105728
Expected: the column is created as numeric(39,0) (or numeric) and holds the exact values, as UBIGINT does since #256.
OS:
Ubuntu 22.04.2 LTS, x86_64
PostgreSQL Version:
17.11
DuckDB Version:
v2.1.0-dev89158 (main 1b4c3e54ba) + duckdb-postgres main c0f5a3c, built from source; also 1.5.6 with the released postgres_scanner
DuckDB Client:
CLI
- Dominant language
- C++
- Stars
- 375
- Forks
- 113
- Avg merge
- 6h 42m
- Merged PRs (30d)
- 30
Getting set up
This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from duckdb/duckdb-postgres
-
Difficulty 3/5 1-2 days Newbie friendliness 62/100
duckdb/duckdb-postgres#618 ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 25/100
duckdb/duckdb-postgres#594 · 4 comments ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 48/100
duckdb/duckdb-postgres#575 ·
Maintainers usually reply within 1 day
-
Difficulty 3/5 1-2 days Newbie friendliness 74/100
duckdb/duckdb-postgres#564 · 1 comment ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 55/100
duckdb/duckdb-postgres#491 · 1 comment ·
Maintainers usually reply within 1 day
All issues in duckdb/duckdb-postgres
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 65/100
Maintainers usually reply within 1 day
-
Bug
Difficulty 2/5 1-3 hours Newbie friendliness 70/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
Maintainers usually reply within 1 day
-
area: ui bug good first issue P2: medium value: medium
Difficulty 2/5 1-3 hours Newbie friendliness 75/100
patrick-hudson/put.gd-desktop#366 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Niko1221/Strata#3673 · 2 comments ·
Maintainers usually reply within 3 days