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, Jul 03 '18 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, Jul 03 '18 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, Jul 03 '18 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, Jul 03 '18 at 18:22

Parfait · Accepted Answer · 2018-07-03T18:26:13.770

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 03 '18 at 18:26

answered Jul 03 '18 at 18:23

Parfait

104,375
17
94
125

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. – Trevor Nederlof Jul 03 '18 at 18:25
Sounds good. Yes, that argument may not be fully documented in RPostgres but are in DBI. – Parfait Jul 03 '18 at 18:27

Adding Numbers to Postgres Database using RPostgres

1 Answers1