0

I use DBI package to write values to my database tables. Database is PostgreSQL. My data looks like this. Some of my values have 0 digits, some have 1:

values in R dataframe

I get this data from reading csv using xlsx library.

I use this code to write data to my table:

DBI::dbWriteTable(conn = con, 
                  name = Id(schema = "schema", table = 'table'), 
                  value = df, 
                  append=T,)

But in database I end up with this:

database values

Column types of min_limit and max_limit in database are numeric.

I tried to use:

DBI::dbWriteTable(conn = con, 
                  name = Id(schema = "schema", table = 'table'), 
                  value = format(df, digits = 2), 
                  append=T,)

But this gives me error:

> Error while preparing parameters   ERROR: column "row_names" of relation "table" does not exist

What do I need to do to write rounded to 2 digits values to database table?

r2evans
  • 141,215
  • 6
  • 77
  • 149
Artem
  • 1
  • 1
  • change the `df` before you use it as input – D.J Dec 29 '22 at 14:50
  • What should I change? – Artem Dec 29 '22 at 14:52
  • format your data frame before writing it to the data base. now you are doing everything in one step and I am not sure the result of `format(df, digits = 2)` works as intended. this is just a guess but you say that wihout the `format()`-function it works so it is likely that it breaks something in your df. what that is I don't know – D.J Dec 29 '22 at 14:58
  • I tried to use `a<-format(df, digits = 2)`, then wrote it, but error is the same. This is just my guess and probably there's other solution. – Artem Dec 29 '22 at 15:01
  • 1
    (1) Using `format(.)` is going to change your column from numeric to a string; I'm inferring that you intend to use these as numbers, so doing this is not good. (2) I can reproduce this, and I think it's a red-herring related to IEEE-754 and how floating-point numbers are stored and represented. Note that if you set `options(digits=2)`, you are likely to see `49`; `options(digits=3)` and you'll see `48.8`. https://stackoverflow.com/q/9508518/3358272 is an R-centric discussion, but it applies to SQL and other prog-languages as well. Can you demonstrate where this fails you? – r2evans Dec 29 '22 at 16:17
  • For instance, I reproduced your table and queried `"select * from tmptable where max_limit < 48.8"`, and _it did not return the first two rows_ (which to me is an intuitive application of `max_limit < 48.8`). – r2evans Dec 29 '22 at 16:18
  • @r2evans. Did you query it in postgres? Or in R? – Artem Dec 29 '22 at 16:39
  • Both, and that's a red herring as well. Sending the `"select ..."` via `dbGetQuery` sends that string unchanged to the database engine, which then parses and executes it, so running it in `dbGetQuery(..)` in R or running it on the command line via `psql` produces the same effect. – r2evans Dec 29 '22 at 16:53
  • I understand your point and understand your concern. I have had similar concerns in the past, but I think it would be beneficial for you to actually materialize the problem: in your normal use of `max_limit`, can you construct any situation where it does not behave as you intuit? For instance, realize that a submitted value of `48.8` is stored as `48.799*97`, and when I run a query of `max_limit < 48.8`, it _also_ interprets that equivalently (but not intentionally/explicitly) as `max_limit < 48.799*97`, so the _effect_ is the same. – r2evans Dec 29 '22 at 16:58
  • I tried this query in my database `select * from src where max_limit = 48.8` It returns 0 rows. – Artem Dec 29 '22 at 17:14

0 Answers0