RPostgres changes number of digits after decimal point

[kirillseva]

Writing this table to database

Browse[1]> head(df)
  c8d6446849216a7aff6c2256ccc96e379 c5a4250bce927ff4ef8ed59093bd878ec cdf69869ec785c7d1b8068b6e287eb32c id
1                                 1            0.56083285459317266941           -1.49170532658561438843  1
2                                 2            0.79464945197105407715           -0.39146524387521386412  2
3                                 3            0.58925445331260561943            0.95434671620049915663  3
4                                 4            0.89141587377525866032           -1.00872132539390979566  4
5                                 5            0.58816246618516743183            1.05403643324296703021  5
6                                 6            0.49537089723162353039            1.20770345842324178243  6

DBI::dbWriteTable(dbconn, tblname, df, append = FALSE, row.names = FALSE)

Yet when I read it from DB (double-checked using psql), I see this

head(df_db)
  id        x         y
1  1 0.560833 -1.491710
2  2 0.794649 -0.391465
3  3 0.589254  0.954347
4  4 0.891416 -1.008720
5  5 0.588162  1.054040
6  6 0.495371  1.207700

Digits got truncated! Any ideas how to solve this?

[hadley]

Please include a minimal reproducible example that actually verifies that the values are different (not just printed differently, e.g. by subtracting the two values)

[kirillseva]

Sure. I only spotted this because I was migrating a package to use RPostgres, and it fails on writing this dataframe: https://github.com/robertzk/cachemeifyoucan/blob/master/tests/testthat/helper-batch_data.R
I'll try to come up with a simpler example in the mean time

[kirillseva]

Here!

Browse[1]> conn
<PqConnection> travis@localhost:5432
Browse[1]> df_ref
  id         x          y
1  1 0.5608329 -1.4917053
2  2 0.7946495 -0.3914652
3  3 0.5892545  0.9543467
4  4 0.8914159 -1.0087213
5  5 0.5881625  1.0540364
Browse[1]> DBI::dbWriteTable(conn, 'will_drop_digits', df_ref)
[1] TRUE
Browse[1]> df_check <- dbReadTable(conn, 'will_drop_digits')
Browse[1]> df_check
  id        x         y
1  1 0.560833 -1.491710
2  2 0.794649 -0.391465
3  3 0.589254  0.954347
4  4 0.891416 -1.008720
5  5 0.588162  1.054040
Browse[1]> df_check$x == df_ref$x
[1] FALSE FALSE FALSE FALSE FALSE
Browse[1]> df_check$x - df_ref$x
[1]  1.454068e-07 -4.519711e-07 -4.533126e-07  1.262247e-07 -4.661852e-07

Output from psql

travis=> select * from will_drop_digits ;
 id |    x     |     y
----+----------+-----------
  1 | 0.560833 |  -1.49171
  2 | 0.794649 | -0.391465
  3 | 0.589254 |  0.954347
  4 | 0.891416 |  -1.00872
  5 | 0.588162 |   1.05404
(5 rows)

[hadley]

Can you please make an example that I can copy and paste into R easily?

[kirillseva]

Don't know what happened, but my tests magically passed today. Closing the issue.

The only thing I've changed in my code was update R and change dbGetQuery(conn, "BEGIN") to dbSendQuery, but I presume something happened in this repo or in the R version.

[github-actions]

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.