Adding Numbers to Postgres Database using RPostgres

Question

I have enjoyed working with the RPostgres (R package) for a while now and it works very well on most things. One thing I recently ran into is it seems to automatically convert numeric types in R into REAL in my Postgres database. The REAL type seems to be very imprecise so I would ideally like to cast my numerics into a type that can handle many more digits (nothing crazy but at least 10 or so). Anyone know how I can accomplish that? Here is an example before of adding numbers...

library(RPostgres)
library(DBI)
library(tibble)

con <- DBI::dbConnect(RPostgres::Postgres(),
                     host = 'localhost',
                     port = 5432,
                     user = 'test',
                     password = '')

test_tbl <- tibble::tibble(number_use =  434.94823992383445)

DBI::dbWriteTable(con, "test_tbl", test_tbl)

Try dbWriteTable(con, "test_tbl", test_tbl, field.types = list(number_use = "numeric(10,10)")) — Parfait
– Parfait, Commented Jul 3, 2018 at 18:12
Interesting, I got the following error but I will keep reading into the field.types parameter... Error in connection_copy_data(conn@ptr, sql, value) : COPY returned error: ERROR: numeric field overflow DETAIL: A field with precision 10, scale 10 must round to an absolute value less than 1. CONTEXT: COPY test_tbl, line 1, column number_use: "434.948239923834" — Trevor Nederlof
– Trevor Nederlof, Commented Jul 3, 2018 at 18:18
I was able to get "numeric(12,8)" working though, seems the left side must be larger than the right? — Trevor Nederlof
– Trevor Nederlof, Commented Jul 3, 2018 at 18:21
I was just about to answer to that effect to test the precision and scale params. It appears precision is total number of digits before and after decimal point, with scale being number of needed decimal digits. — Parfait
– Parfait, Commented Jul 3, 2018 at 18:22

Parfait · Accepted Answer · 2018-07-03 18:26:13Z

1

Though part of the DBI specifications that may or may not be integated into specific API, here being RPostgres, consider field.types argument, passing a named list of column names and types.

Below uses Postgres NUMERIC(precision, scale) type (synonymous to DECIMAL) where precision is total number of digits for value both before and after decimal point with scale being number of needed decimal digits.

# WITH NUMERIC(precision, scale)
dbWriteTable(con, "test_tbl", test_tbl, field.types=list(number_use="numeric(##,##)"))

edited Jul 3, 2018 at 18:26

answered Jul 3, 2018 at 18:23

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trevor Nederlof Over a year ago

Thank you for the answer, I could not figure out the field.types parameter. This should allow me to at least get hte numerical precision I want as long as I specify the field.types.

Parfait Over a year ago

Sounds good. Yes, that argument may not be fully documented in RPostgres but are in DBI.

Collectives™ on Stack Overflow

Adding Numbers to Postgres Database using RPostgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related