Can't connect - Error: Decimal with a width of 255 is bigger than the maximum supported of 38.

[bassamsdata]

unfortunately, I couldn't connect to any of my postgresql databases through the duckdb scanner due to 2 types of errors.

first one: Decimal with a width of 255 is bigger than the maximum supported of 38.
I tried with both mechanisms, to connect to the database and to connect to just a table.
call postgres_attach('dbname=bassam');
select * from postgres_scan('dbname=bassam', 'public', 'test');

the only database that I could able to connect to is the one that having 0 tables.

the second problem: is that the database has not been invalidated!
D select * from postgres_scan('dbname=bassam', 'public', 'untitled_table');

you can replicate the issue with the this dataset below:
https://github.com/bassamsdata/earthquake_data/blob/main/earthquake.csv

postgresql version: 14.5
duckdb version: 0.5.1
macOS: 12.6

thanks a lot for your efforts.

[Mytherin]

the second problem: is that the database has not been invalidated!

The database has been invalidated message comes from DuckDB itself, it does not relate to the Postgres database. This error happens after an internal exception is triggered. That is because internal exceptions are not expected (i.e. essentially assertions). As the system might be in an undefined state, we disable any operations on the database to prevent potential problems or data corruptions.

The error message should be made more clear - I will work on that.

[hannes]

Can you try to make a reproducible example of this please?

[bassamsdata]

Yea sure, I'll give the exact steps along with the databases to reproduce the issue.

[theelderbeever]

Is there any movement on this issue?

Steps to reproduce would be to

1. Create a postgres table with a column with a defined NUMERIC size >38. ie: CREATE TABLE big ( name TEXT, value NUMERIC(78,0) )
2. Attempt to connect to postgres with duckdb's postgres scanner. Doesn't matter which method. They all exhibit the same behavior.

Duckdb will fail to connect providing the DECIMAL width error in the original issue. If you don't don't define the size of the NUMERIC column in postgres it will connect but, will overflow on widths >38 as mentioned in numerous issues.

The most annoying part of this connection issue is that when using POSTGRES_SCAN or POSTGRES_SCAN_PUSHDOWN the error occurs regardless of whether you even access the NUMERIC column. And additionally prevents connecting and coercing the column. ie: using other tools like connectorx which pushes the full query I have commonly done my_big_number::{TEXT, FLOAT}. While those don't preserve precision it at least allows me to operate on the column.

[Mytherin]

Thanks for the report! This should be fixed now in #111.