-
Notifications
You must be signed in to change notification settings - Fork 93
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Bulk Ingestion into Postgresql Numeric with scale #2177
Comments
Does it matter if you use decimal128 versus decimal256? The former should work just fine with the provided precision / scale |
Sorry @WillAyd , It's an error when "amount" value like this "12222.00" or that have decimal with zero value. |
I think this is because the bulk insert feature requires that the schemas match exactly and because we don't take into account the existing column types when performing a bulk insertion (we should!). Specifically, that would mean that we should add a arrow-adbc/c/driver/postgresql/copy/writer.h Lines 568 to 571 in 46dc748
...and return our field writers based on a many-to-many mapping (e.g., so that we can generate valid COPY for a numeric type, for example, based on a variety of types of arrow inputs. In the meantime, you should be able to use a parameterized |
I think you are looking for something like this: tbl = pa.Table(...)
with adbc_driver_postgresql.dbapi(conn_args) as conn, conn.cursor() as cur:
cur.adbc_prepare("INSERT INTO some_table VALUES (?)")
cur.executemany("INSERT INTO some_table VALUES (?)", tbl)
conn.commit() |
This won't be as fast as the bulk insert, just to be clear (but I think it should be a bit better than other drivers since we can still at least use the binary format? It's only that we have to do a server roundtrip for each row in this case) |
I try to do another approach to tackle insert "Decimal" data into Postgresql. I think that's enough for me, even must add doing "decimal transformation". Ty guys, for help. |
What would you like help with?
Env:
Background:
I want to load data into Postgresql using Bulk Ingestion/Copy command from parquet file.
I have pyarrow "amount" column with pa.float64(), to be inserted into Numeric with scale column in Postgresql "amount numeric(18, 2)".
Then I got this error below:
Exception: ProgrammingError('INVALID_ARGUMENT: [libpq] Failed to execute COPY statement: PGRES_FATAL_ERROR ERROR: insufficient data left in message\nCONTEXT: COPY invoice_new, line 1, column amount\n. SQLSTATE: 08P01')
And I tried to change pyarrow schema "amount" column into "decimal256(18, 2)"
I got this error below:
Exception: ProgrammingError('INVALID_ARGUMENT: [libpq] Failed to execute COPY statement: PGRES_FATAL_ERROR ERROR: invalid scale in external "numeric" value\nCONTEXT: COPY invoice_new, line 1, column amount\n. SQLSTATE: 22P03')
To fix this error, do I need cast pyarrow "amount" column schema to another schema type?
Ty
The text was updated successfully, but these errors were encountered: