Skip to content
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

Odd rounding on write table #184

Closed
JohnMount opened this issue Apr 17, 2018 · 4 comments
Closed

Odd rounding on write table #184

JohnMount opened this issue Apr 17, 2018 · 4 comments
Labels

Comments

@JohnMount
Copy link

From tidyverse/dplyr#3516 . Notice how violently the value is rounded when round-tripped through the database. The amount of rounding depends on the driver (so may not be a pure DBI issue).

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

tmp_df <- data.frame(id =  1363392673615939)
format(tmp_df, scientific = FALSE)
#>                 id
#> 1 1363392673615939
str(tmp_df)
#> 'data.frame':    1 obs. of  1 variable:
#>  $ id: num 1.36e+15
DBI::dbWriteTable(db, "tmp_df", tmp_df, 
                  temporary = TRUE, overwrite = TRUE)
d <- DBI::dbGetQuery(db, "SELECT * FROM tmp_df")
format(d, scientific = FALSE)
#>                 id
#> 1 1363390000000000
DBI::dbGetQuery(db,
                "select table_name, column_name, data_type from information_schema.columns
                where table_name = 'tmp_df' order by table_name, column_name")
#>   table_name column_name data_type
#> 1     tmp_df          id      real
DBI::dbDisconnect(db)
@krlmlr
Copy link
Member

krlmlr commented Apr 30, 2018

Thanks. The rounding to six digits shouldn't occur with the new dbAppendTable(), can you confirm?

@krlmlr krlmlr added the bug label Apr 30, 2018
@JohnMount
Copy link
Author

Does not seem to make a difference. I just installed DBI and RPostgres and got a similar result.

# devtools::install_github("r-dbi/RPostgres")
# devtools::install_github("r-dbi/DBI")
packageVersion("RPostgres")
## [1] '1.1.0.9001'
packageVersion("DBI")
## [1] '1.0.0'
library("DBI")
library("RPostgres")

db <- dbConnect(RPostgres::Postgres(),
                     host = 'localhost',
                     port = 5432,
                     user = 'johnmount',
                     password = '')

tmp_df <- data.frame(id =  1363392673615939)
format(tmp_df, scientific = FALSE)
##                 id
## 1 1363392673615939
str(tmp_df)
## 'data.frame':    1 obs. of  1 variable:
##  $ id: num 1.36e+15
dbCreateTable(db, "tmp_df", tmp_df, 
                  temporary = TRUE, overwrite = TRUE)
dbAppendTable(db, "tmp_df", tmp_df)
## [1] 1
d <- dbGetQuery(db, "SELECT * FROM tmp_df")
format(d, scientific = FALSE)
##                 id
## 1 1363390000000000
dbGetQuery(db,
                "select table_name, column_name, data_type from information_schema.columns
                where table_name = 'tmp_df' order by table_name, column_name")
##   table_name column_name data_type
## 1     tmp_df          id      real
dbDisconnect(db)

@krlmlr
Copy link
Member

krlmlr commented Aug 26, 2018

Thanks again. The underlying issue seems to be that numbers are formatted with the default significant digits before being passed to libpq.

@github-actions
Copy link

github-actions bot commented Dec 7, 2020

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.

@github-actions github-actions bot locked and limited conversation to collaborators Dec 7, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Projects
None yet
Development

No branches or pull requests

2 participants