<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/take-care-if-trying-the-rpostgres-package/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2018/03/take-care-if-trying-the-rpostgres-package/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Take care if trying the new <a rel="nofollow" href="https://cran.r-project.org/package=RPostgres" target="_blank"><code>RPostgres</code></a> database connection package. By default it returns some non-standard types that code developed against other database drivers may not expect, and may not be ready to defend against.</p>
<p><center><br>
<img border="0" src="https://i1.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/03/Danger.gif?resize=582%2C430" alt="Danger" width="450" title="Danger.gif" data-recalc-dims="1">
<p>
<p>Danger, Will Robinson!<br>
</p>
<p><span></span></p>
<h2>Trying the new package</h2>
<p>One can try the newer <a rel="nofollow" href="https://cran.r-project.org/package=RPostgres" target="_blank"><code>RPostgres</code></a> as a drop-in replacement for the usual <a rel="nofollow" href="https://cran.r-project.org/package=RPostgreSQL" target="_blank"><code>RPostgreSQL</code></a>.</p>
<p>That starts out okay. We can connect to the database and and pull a summary about remote data to <code>R</code>.</p>
<div>
<pre><code>db &lt;-<span> </span>DBI::<span>dbConnect</span>(
  RPostgres::<span>Postgres</span>(),
  <span>host =</span> <span>'localhost'</span>,
  <span>port =</span> <span>5432</span>,
  <span>user =</span> <span>'johnmount'</span>,
  <span>password =</span> <span>''</span>)</code></pre>
</div>
<pre><code>## Warning: multiple methods tables found for 'dbQuoteLiteral'</code></pre>
<div>
<pre><code>d &lt;-<span> </span>DBI::<span>dbGetQuery</span>(
  db, 
  <span>&quot;SELECT COUNT(1) FROM pg_catalog.pg_tables&quot;</span>)
<span>print</span>(d)</code></pre>
</div>
<pre><code>##   count
## 1   177</code></pre>
<div>
<pre><code>ntables &lt;-<span> </span>d$count[[<span>1</span>]]
<span>print</span>(ntables)</code></pre>
</div>
<pre><code>## integer64
## [1] 177</code></pre>
<p>The result at first looks okay.</p>
<div>
<pre><code><span>class</span>(ntables)</code></pre>
</div>
<pre><code>## [1] &quot;integer64&quot;</code></pre>
<div>
<pre><code><span>typeof</span>(ntables)</code></pre>
</div>
<pre><code>## [1] &quot;double&quot;</code></pre>
<div>
<pre><code>ntables +<span> </span>1L</code></pre>
</div>
<pre><code>## integer64
## [1] 178</code></pre>
<div>
<pre><code>ntables +<span> </span><span>1</span></code></pre>
</div>
<pre><code>## integer64
## [1] 178</code></pre>
<div>
<pre><code><span>is.numeric</span>(ntables)</code></pre>
</div>
<pre><code>## [1] TRUE</code></pre>
<p>But it is only okay, until it is not.</p>
<div>
<pre><code><span>pmax</span>(1L, ntables)</code></pre>
</div>
<pre><code>## [1] 8.744962e-322</code></pre>
<div>
<pre><code><span>pmin</span>(1L, ntables)</code></pre>
</div>
<pre><code>## [1] 1</code></pre>
<div>
<pre><code><span>ifelse</span>(<span>TRUE</span>, ntables, ntables)</code></pre>
</div>
<pre><code>## [1] 8.744962e-322</code></pre>
<div>
<pre><code>for(ni in ntables) {
  <span>print</span>(ni)
}</code></pre>
</div>
<pre><code>## [1] 8.744962e-322</code></pre>
<div>
<pre><code><span>unclass</span>(ntables)</code></pre>
</div>
<pre><code>## [1] 8.744962e-322</code></pre>
<p>If your code, or any package code you are using, perform <em>any</em> of the above calculations, your results will be corrupt and wrong. It is quite likely any code written before December 2017 (<a rel="nofollow" href="https://cran.rstudio.com/src/contrib/Archive/RPostgres/" target="_blank"><code>RPostgres</code>‘s first <code>CRAN</code> distribution</a>) would not have been written with the <code>RPostgres</code> &quot;<strong><code>integer64</code> for all of my friends</strong>&quot; design decision in mind.</p>
<p>Also note, <code>RPostgres</code> does not currently appear to write <code>integer64</code> back to the database.</p>
<div>
<pre><code>DBI::<span>dbWriteTable</span>(db, <span>&quot;d&quot;</span>, d, 
                  <span>temporary =</span> <span>TRUE</span>, 
                  <span>overwrite =</span> <span>TRUE</span>)
DBI::<span>dbGetQuery</span>(db, <span>&quot;</span>
<span> SELECT </span>
<span> column_name, </span>
<span> data_type, </span>
<span> numeric_precision, </span>
<span> numeric_precision_radix,</span>
<span> udt_name</span>
<span> FROM </span>
<span> information_schema.columns </span>
<span> WHERE </span>
<span> table_name = 'd'</span>
<span> &quot;</span>)</code></pre>
</div>
<pre><code>##   column_name data_type numeric_precision numeric_precision_radix udt_name
## 1       count      real                24                       2   float4</code></pre>
<div>
<pre><code>DBI::<span>dbDisconnect</span>(db)</code></pre>
</div>
<h2>The work-around</h2>
<p>The work-around is: add the argument <code>bigint = &quot;numeric&quot;</code> to your <code>dbConnect()</code> call. This is mentioned in the <a rel="nofollow" href="https://cran.r-project.org/web/packages/RPostgres/RPostgres.pdf" target="_blank">manual</a>, but not the default and not called out in the package description or <a rel="nofollow" href="https://cran.r-project.org/web/packages/RPostgres/README.html" target="_blank"><code>README</code></a>. Or, of course, you could use <a rel="nofollow" href="https://cran.r-project.org/package=RPostgreSQL" target="_blank"><code>RPostgreSQL</code></a>.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://www.win-vector.com/blog/2018/03/take-care-if-trying-the-rpostgres-package/"> R – Win-Vector Blog</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/b2GBwG2uiPI" height="1"></a></div></center>