1

I am having an issue getting NaN numeric data with dplyr (0.5.0) from a PostgreSQL column of type numeric (and thus supports NaN). The underlying fault is in RPostgreSQL, which reads NaN values as zero. E.g.:

# create table
create table tmp_dplyr_NaN_test_20170802 (
val numeric
);

insert into tmp_dplyr_NaN_test_20170802 (val) values (100);
insert into tmp_dplyr_NaN_test_20170802 (val) values ('NaN');

Then, running select * from tmp_dplyr_NaN_test_20170802; returns the values 100, NaN as expected.

In R:

RPostgreSQL::dbGetQuery(con, "select val from tmp_dplyr_NaN_test_20170802")
# val
# 1 100
# 2   0

which can be 'fixed' by:

RPostgreSQL::dbGetQuery(con, "select nullif(val, 'NaN') from tmp_dplyr_NaN_test_20170802")

#     nullif
# 1    100
# 2     NA

I am wondering what is going on under the hood here. How does one actually collect NaN values as NaN or NA without resorting to nullif inside the database?

Alex
  • 15,186
  • 15
  • 73
  • 127

0 Answers0