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.

Sign up for GitHub

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

Jump to bottom

Truncated digits #194

Closed
etiennebr opened this issue Sep 5, 2018 · 6 comments
Closed

Truncated digits #194

etiennebr opened this issue Sep 5, 2018 · 6 comments
Labels
bug

Comments

@etiennebr
Copy link
Contributor

etiennebr commented Sep 5, 2018 •
edited
Loading

Digits are truncated by RPostgres compared to RpostgreSQL. This is identical to #33 (which couldn't be reproduced).

# reprex
library(DBI)
library(tidyverse)

con <- dbConnect(  
  RPostgres::Postgres()
)

a <- tibble(
  x = c(-77.162211, -84.213210, -97.267341, -77.452213, -23.23263, -75.44221),
  y = c(45.252653, 45.252653, 45.252655, 45.252656, 45.252658, 45.252658),
  z = c(-0.600656995283962, 0.208829918838919, -1.65672271411391, -1.07972616623455, -1.89209297427932, 0.214198876049621)
)

dbWriteTable(con, "a", a, overwrite = TRUE, temporary = TRUE)
b <- dbReadTable(con, "a")
bind_cols(a, b) %>%
  mutate(x - x1,
         y - y1,
         z - z1)
#> # A tibble: 6 x 9
#>       x     y      z    x1    y1     z1   `x - x1`   `y - y1` `z - z1`
#>   <dbl> <dbl>  <dbl> <dbl> <dbl>  <dbl>      <dbl>      <dbl>    <dbl>
#> 1 -77.2  45.3 -0.601 -77.2  45.3 -0.601 -0.000011  -0.0000470  4.72e-9
#> 2 -84.2  45.3  0.209 -84.2  45.3  0.209 -0.00001   -0.0000470 -8.12e-8
#> 3 -97.3  45.3 -1.66  -97.3  45.3 -1.66  -0.0000410 -0.000045  -2.71e-6
#> 4 -77.5  45.3 -1.08  -77.5  45.3 -1.08  -0.0000130 -0.0000440  3.83e-6
#> 5 -23.2  45.3 -1.89  -23.2  45.3 -1.89  -0.0000300 -0.000042  -2.97e-6
#> 6 -75.4  45.3  0.214 -75.4  45.3  0.214 -0.00001   -0.000042  -1.24e-7

# compare with RPostgreSQL ---------------------------------------------
con <- dbConnect(
  RPostgreSQL::PostgreSQL(),
)
dbWriteTable(con, "a", a, overwrite = TRUE, temporary = TRUE)
#> [1] TRUE
b <- dbReadTable(con, "a")
bind_cols(a, b) %>%
  mutate(x - x1,
         y - y1,
         z - z1)
#> # A tibble: 6 x 9
#>       x     y      z    x1    y1     z1 `x - x1` `y - y1` `z - z1`
#>   <dbl> <dbl>  <dbl> <dbl> <dbl>  <dbl>    <dbl>    <dbl>    <dbl>
#> 1 -77.2  45.3 -0.601 -77.2  45.3 -0.601        0        0        0
#> 2 -84.2  45.3  0.209 -84.2  45.3  0.209        0        0        0
#> 3 -97.3  45.3 -1.66  -97.3  45.3 -1.66         0        0        0
#> 4 -77.5  45.3 -1.08  -77.5  45.3 -1.08         0        0        0
#> 5 -23.2  45.3 -1.89  -23.2  45.3 -1.89         0        0        0
#> 6 -75.4  45.3  0.214 -75.4  45.3  0.214        0        0        0
Session info 
devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value
#>  version  R version 3.4.4 (2018-03-15)
#>  system   x86_64, linux-gnu
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  tz       America/New_York
#>  date     2018-09-05
#> Packages -----------------------------------------------------------------
#>  package     * version    date       source
#>  assertthat    0.2.0      2017-04-11 CRAN (R 3.4.0)
#>  backports     1.1.2      2017-12-13 cran (@1.1.2)
#>  base        * 3.4.4      2018-03-16 local
#>  bindr         0.1.1      2018-03-13 CRAN (R 3.4.4)
#>  bindrcpp    * 0.2.2      2018-03-29 CRAN (R 3.4.4)
#>  bit           1.1-13     2018-05-15 CRAN (R 3.4.4)
#>  bit64         0.9-7      2017-05-08 CRAN (R 3.4.0)
#>  blob          1.1.1      2018-03-25 cran (@1.1.1)
#>  broom         0.4.4      2018-03-29 CRAN (R 3.4.4)
#>  cellranger    1.1.0      2016-07-27 cran (@1.1.0)
#>  cli           1.0.0      2017-11-05 CRAN (R 3.4.2)
#>  colorspace    1.3-2      2016-12-14 CRAN (R 3.4.0)
#>  compiler      3.4.4      2018-03-16 local
#>  crayon        1.3.4      2018-02-13 Github (gaborcsardi/crayon@95b3eae)
#>  datasets    * 3.4.4      2018-03-16 local
#>  DBI         * 1.0.0      2018-05-02 CRAN (R 3.4.4)
#>  devtools      1.13.5     2018-02-18 CRAN (R 3.4.3)
#>  digest        0.6.15     2018-01-28 cran (@0.6.15)
#>  dplyr       * 0.7.5      2018-04-18 Github (tidyverse/dplyr@f2226b0)
#>  evaluate      0.10.1     2017-06-24 cran (@0.10.1)
#>  forcats     * 0.3.0      2018-02-19 CRAN (R 3.4.4)
#>  foreign       0.8-70     2018-04-23 CRAN (R 3.4.4)
#>  ggplot2     * 2.2.1.9000 2018-05-10 Github (tidyverse/ggplot2@31322d8)
#>  glue          1.2.0      2017-10-29 CRAN (R 3.4.3)
#>  graphics    * 3.4.4      2018-03-16 local
#>  grDevices   * 3.4.4      2018-03-16 local
#>  grid          3.4.4      2018-03-16 local
#>  gtable        0.2.0      2016-02-26 CRAN (R 3.4.0)
#>  haven         1.1.1      2018-01-18 CRAN (R 3.4.4)
#>  hms           0.4.2      2018-03-10 cran (@0.4.2)
#>  htmltools     0.3.6      2017-04-28 CRAN (R 3.4.0)
#>  httr          1.3.1      2017-08-20 CRAN (R 3.4.2)
#>  jsonlite      1.5        2017-06-01 cran (@1.5)
#>  knitr         1.20       2018-02-20 CRAN (R 3.4.4)
#>  lattice       0.20-35    2017-03-25 CRAN (R 3.4.4)
#>  lazyeval      0.2.1      2017-10-29 cran (@0.2.1)
#>  lubridate     1.7.4      2018-04-11 CRAN (R 3.4.4)
#>  magrittr      1.5        2014-11-22 CRAN (R 3.4.0)
#>  memoise       1.1.0      2018-02-13 Github (hadley/memoise@611cfad)
#>  methods     * 3.4.4      2018-03-16 local
#>  mnormt        1.5-5      2016-10-15 cran (@1.5-5)
#>  modelr        0.1.2      2018-05-11 CRAN (R 3.4.4)
#>  munsell       0.5.0      2018-06-12 cran (@0.5.0)
#>  nlme          3.1-137    2018-04-07 CRAN (R 3.4.4)
#>  parallel      3.4.4      2018-03-16 local
#>  pillar        1.2.2      2018-04-26 CRAN (R 3.4.4)
#>  pkgconfig     2.0.1      2017-03-21 CRAN (R 3.4.0)
#>  plyr          1.8.4      2016-06-08 CRAN (R 3.4.0)
#>  psych         1.8.4      2018-05-06 CRAN (R 3.4.4)
#>  purrr       * 0.2.5      2018-05-29 cran (@0.2.5)
#>  R6            2.2.2      2017-06-17 cran (@2.2.2)
#>  Rcpp          0.12.18    2018-07-23 cran (@0.12.18)
#>  readr       * 1.1.1      2017-05-16 cran (@1.1.1)
#>  readxl        1.1.0      2018-04-20 CRAN (R 3.4.4)
#>  reshape2      1.4.3      2017-12-11 cran (@1.4.3)
#>  rlang         0.2.2.9000 2018-08-27 Github (r-lib/rlang@048262d)
#>  rmarkdown     1.9        2018-03-01 CRAN (R 3.4.4)
#>  RODBC         1.3-15     2017-05-05 CRAN (R 3.4.4)
#>  RPostgres     1.1.1      2018-05-30 Github (r-dbi/rpostgres@7fe22ca)
#>  RPostgreSQL   0.6-2      2017-06-24 CRAN (R 3.4.2)
#>  rprojroot     1.3-2      2018-01-03 cran (@1.3-2)
#>  rstudioapi    0.7        2017-09-07 CRAN (R 3.4.2)
#>  rvest         0.3.2      2016-06-17 cran (@0.3.2)
#>  scales        1.0.0.9000 2018-08-27 Github (hadley/scales@0f7a186)
#>  stats       * 3.4.4      2018-03-16 local
#>  stringi       1.2.2      2018-05-02 CRAN (R 3.4.4)
#>  stringr     * 1.3.1      2018-05-10 CRAN (R 3.4.4)
#>  tibble      * 1.4.2      2018-01-22 cran (@1.4.2)
#>  tidyr       * 0.8.0      2018-01-29 cran (@0.8.0)
#>  tidyselect    0.2.4      2018-02-26 cran (@0.2.4)
#>  tidyverse   * 1.2.1      2018-04-17 Github (tidyverse/tidyverse@03ccf9c)
#>  tools         3.4.4      2018-03-16 local
#>  utf8          1.1.3      2018-01-03 cran (@1.1.3)
#>  utils       * 3.4.4      2018-03-16 local
#>  withr         2.1.2      2018-05-30 Github (jimhester/withr@70d6321)
#>  xml2          1.2.0      2018-01-24 cran (@1.2.0)
#>  yaml          2.1.19     2018-05-01 cran (@2.1.19)
@MikeBadescu
Copy link

Could you please try the dev version from GitHub (remotes::install_github("r-dbi/RPostgres"))? I suspect this is related to #184.

@etiennebr
Copy link
Contributor Author

I tried on another db instance, with the latest version from github, but it doesn't seem to make a difference.

# reprex
library(DBI)
library(tidyverse)

con <- dbConnect(
    RPostgres::Postgres(), 
 )

DBI::dbGetInfo(con)$server_version
#> [1] 100003

a <- tibble(
    x = c(-77.162211, -84.213210, -97.267341, -77.452213, -23.23263, -75.44221),
    y = c(45.252653, 45.252653, 45.252655, 45.252656, 45.252658, 45.252658),
    z = c(-0.600656995283962, 0.208829918838919, -1.65672271411391, -1.07972616623455, -1.89209297427932, 0.214198876049621)
)

dbWriteTable(con, "a", a, overwrite = TRUE, temporary = TRUE)
b <- dbReadTable(con, "a")
bind_cols(a, b) %>%
    mutate(x - x1,
           y - y1,
           z - z1)
#> # A tibble: 6 x 9
#>       x     y      z    x1    y1     z1   `x - x1`   `y - y1` `z - z1`
#>   <dbl> <dbl>  <dbl> <dbl> <dbl>  <dbl>      <dbl>      <dbl>    <dbl>
#> 1 -77.2  45.3 -0.601 -77.2  45.3 -0.601 -0.000011  -0.0000470  4.72e-9
#> 2 -84.2  45.3  0.209 -84.2  45.3  0.209 -0.00001   -0.0000470 -8.12e-8
#> 3 -97.3  45.3 -1.66  -97.3  45.3 -1.66  -0.0000410 -0.000045  -2.71e-6
#> 4 -77.5  45.3 -1.08  -77.5  45.3 -1.08  -0.0000130 -0.0000440  3.83e-6
#> 5 -23.2  45.3 -1.89  -23.2  45.3 -1.89  -0.0000300 -0.000042  -2.97e-6
#> 6 -75.4  45.3  0.214 -75.4  45.3  0.214 -0.00001   -0.000042  -1.24e-7

# compare with RPostgreSQL ---------------------------------------------
con <- dbConnect(
    RPostgreSQL::PostgreSQL(), 
)
dbWriteTable(con, "a", a, overwrite = TRUE, temporary = TRUE)
#> [1] TRUE
b <- dbReadTable(con, "a")
bind_cols(a, b) %>%
    mutate(x - x1,
           y - y1,
           z - z1)
#> # A tibble: 6 x 9
#>       x     y      z    x1    y1     z1 `x - x1` `y - y1` `z - z1`
#>   <dbl> <dbl>  <dbl> <dbl> <dbl>  <dbl>    <dbl>    <dbl>    <dbl>
#> 1 -77.2  45.3 -0.601 -77.2  45.3 -0.601        0        0        0
#> 2 -84.2  45.3  0.209 -84.2  45.3  0.209        0        0        0
#> 3 -97.3  45.3 -1.66  -97.3  45.3 -1.66         0        0        0
#> 4 -77.5  45.3 -1.08  -77.5  45.3 -1.08         0        0        0
#> 5 -23.2  45.3 -1.89  -23.2  45.3 -1.89         0        0        0
#> 6 -75.4  45.3  0.214 -75.4  45.3  0.214        0        0        0

Created on 2018-09-17 by the reprex package (v0.2.0).

Session info 
devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.5.1 (2018-07-02)
#>  system   x86_64, linux-gnu           
#>  ui       X11                         
#>  language (EN)                        
#>  collate  en_CA.UTF-8                 
#>  tz       America/Toronto             
#>  date     2018-09-17
#> Packages -----------------------------------------------------------------
#>  package     * version    date       source                          
#>  assertthat    0.2.0      2017-04-11 CRAN (R 3.5.1)                  
#>  backports     1.1.2      2017-12-13 CRAN (R 3.5.1)                  
#>  base        * 3.5.1      2018-07-03 local                           
#>  bindr         0.1.1      2018-03-13 CRAN (R 3.5.1)                  
#>  bindrcpp    * 0.2.2      2018-03-29 CRAN (R 3.5.1)                  
#>  bit           1.1-14     2018-05-29 CRAN (R 3.5.1)                  
#>  bit64         0.9-7      2017-05-08 CRAN (R 3.5.1)                  
#>  blob          1.1.1      2018-03-25 CRAN (R 3.5.1)                  
#>  broom         0.5.0      2018-07-17 CRAN (R 3.5.1)                  
#>  cellranger    1.1.0      2016-07-27 CRAN (R 3.5.1)                  
#>  cli           1.0.0      2017-11-05 CRAN (R 3.5.1)                  
#>  colorspace    1.3-2      2016-12-14 CRAN (R 3.5.1)                  
#>  compiler      3.5.1      2018-07-03 local                           
#>  crayon        1.3.4      2017-09-16 CRAN (R 3.5.1)                  
#>  datasets    * 3.5.1      2018-07-03 local                           
#>  DBI         * 1.0.0.9000 2018-09-18 Github (r-dbi/DBI@b5226cc)      
#>  devtools      1.13.6     2018-06-27 CRAN (R 3.5.1)                  
#>  digest        0.6.16     2018-08-22 CRAN (R 3.5.1)                  
#>  dplyr       * 0.7.6      2018-06-29 CRAN (R 3.5.1)                  
#>  evaluate      0.11       2018-07-17 CRAN (R 3.5.1)                  
#>  fansi         0.3.0      2018-08-13 CRAN (R 3.5.1)                  
#>  forcats     * 0.3.0      2018-02-19 CRAN (R 3.5.1)                  
#>  ggplot2     * 3.0.0      2018-07-03 CRAN (R 3.5.1)                  
#>  glue          1.3.0      2018-07-17 CRAN (R 3.5.1)                  
#>  graphics    * 3.5.1      2018-07-03 local                           
#>  grDevices   * 3.5.1      2018-07-03 local                           
#>  grid          3.5.1      2018-07-03 local                           
#>  gtable        0.2.0      2016-02-26 CRAN (R 3.5.1)                  
#>  haven         1.1.2      2018-06-27 CRAN (R 3.5.1)                  
#>  hms           0.4.2      2018-03-10 CRAN (R 3.5.1)                  
#>  htmltools     0.3.6      2017-04-28 CRAN (R 3.5.1)                  
#>  httr          1.3.1      2017-08-20 CRAN (R 3.5.1)                  
#>  jsonlite      1.5        2017-06-01 CRAN (R 3.5.1)                  
#>  knitr         1.20       2018-02-20 CRAN (R 3.5.1)                  
#>  lattice       0.20-35    2017-03-25 CRAN (R 3.5.0)                  
#>  lazyeval      0.2.1      2017-10-29 CRAN (R 3.5.1)                  
#>  lubridate     1.7.4      2018-04-11 CRAN (R 3.5.1)                  
#>  magrittr      1.5        2014-11-22 CRAN (R 3.5.1)                  
#>  memoise       1.1.0      2017-04-21 CRAN (R 3.5.1)                  
#>  methods     * 3.5.1      2018-07-03 local                           
#>  modelr        0.1.2      2018-05-11 CRAN (R 3.5.1)                  
#>  munsell       0.5.0      2018-06-12 CRAN (R 3.5.1)                  
#>  nlme          3.1-137    2018-04-07 CRAN (R 3.5.0)                  
#>  pillar        1.3.0      2018-07-14 CRAN (R 3.5.1)                  
#>  pkgconfig     2.0.2      2018-08-16 CRAN (R 3.5.1)                  
#>  plyr          1.8.4      2016-06-08 CRAN (R 3.5.1)                  
#>  purrr       * 0.2.5      2018-05-29 CRAN (R 3.5.1)                  
#>  R6            2.2.2      2017-06-17 CRAN (R 3.5.1)                  
#>  Rcpp          0.12.18    2018-07-23 CRAN (R 3.5.1)                  
#>  readr       * 1.1.1      2017-05-16 CRAN (R 3.5.1)                  
#>  readxl        1.1.0      2018-04-20 CRAN (R 3.5.1)                  
#>  rlang         0.2.2      2018-08-16 CRAN (R 3.5.1)                  
#>  rmarkdown     1.10       2018-06-11 CRAN (R 3.5.1)                  
#>  RPostgres     1.1.1.9001 2018-09-18 Github (r-dbi/RPostgres@9e6e6cc)
#>  RPostgreSQL   0.6-2      2017-06-24 cran (@0.6-2)                   
#>  rprojroot     1.3-2      2018-01-03 CRAN (R 3.5.1)                  
#>  rvest         0.3.2      2016-06-17 CRAN (R 3.5.1)                  
#>  scales        1.0.0      2018-08-09 CRAN (R 3.5.1)                  
#>  stats       * 3.5.1      2018-07-03 local                           
#>  stringi       1.2.4      2018-07-20 CRAN (R 3.5.1)                  
#>  stringr     * 1.3.1      2018-05-10 CRAN (R 3.5.1)                  
#>  tibble      * 1.4.2      2018-01-22 CRAN (R 3.5.1)                  
#>  tidyr       * 0.8.1      2018-05-18 CRAN (R 3.5.1)                  
#>  tidyselect    0.2.4      2018-02-26 CRAN (R 3.5.1)                  
#>  tidyverse   * 1.2.1      2017-11-14 CRAN (R 3.5.1)                  
#>  tools         3.5.1      2018-07-03 local                           
#>  utf8          1.1.4      2018-05-24 CRAN (R 3.5.1)                  
#>  utils       * 3.5.1      2018-07-03 local                           
#>  withr         2.1.2      2018-03-15 CRAN (R 3.5.1)                  
#>  xml2          1.2.0      2018-01-24 CRAN (R 3.5.1)                  
#>  yaml          2.2.0      2018-07-25 CRAN (R 3.5.1)

@krlmlr
Copy link
Member

krlmlr commented Nov 26, 2018

Thanks, confirmed.

@krlmlr krlmlr added the bug label Nov 26, 2018
@krlmlr
Copy link
Member

krlmlr commented Nov 26, 2018

Numeric values are translated to SQL REAL, which is a 4-byte floating point value on PostgreSQL. Use field.types in dbWriteTable() or a named vector as fields for dbCreateTable() to work around.

@krlmlr krlmlr closed this as completed in 67449d5 Nov 26, 2018
@tomekziel
Copy link

@krlmlr why the default floating point type in RPostgres is REAL and not DOUBLE PRECISION?

@krlmlr krlmlr mentioned this issue Dec 17, 2019
Closed
@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.
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@etiennebr @krlmlr @tomekziel @MikeBadescu