RPostgres query works interactively, in notebook, but will not knit

postgres
postgresql

#1

The following code works interactively in RStudio giving a count of 46520 from a Run All.

One can Preview Notebook | Run Restart R and clear output | Run All | Preview

The .nb.html file looks fine with a count of 46520. [I'd attach it here, but that's not allowed.]

count is displayed as a "S3: integer64" if that matters.

But any attempt to Knit to HTML fails. The Output tab shows (with the same text showing in the Issues tab):

Quitting from lines 23-25 (Querying-MIMIC-III-problem.Rmd)
Error in if (n2%%n1) warning("length(e2) not a multiple length(e1)") :
  argument is not interpretable as logical
Calls: <Anonymous> ... withVisible -> eval -> eval -> ==.integer64 -> binattr
Execution halted
---
title: "Querying MIMIC-III"
output:
  html_document:
    toc: yes
  html_notebook:
    toc: yes
---

```{r}
library(DBI)
library(RPostgres)

MimicDB <- dbConnect(RPostgres::Postgres(),
                     host     = "localhost",
                     dbname   = "mimic",
                     user     = Sys.getenv("MIMIC_User"),
                     password = Sys.getenv("MIMIC_Password"),
                     options  = "-c search_path=mimiciii")
```

```{sql, connection=MimicDB}
SELECT COUNT(*)
FROM   patients
```

## Close database

```{r, comment=NA}
dbDisconnect(MimicDB)
```

Why can Preview work, but knit fails?

Any suggestions on how to troubleshoot?


#2

What is the options statement doing? Can you write around it? It’s the only thing that stands out.


#3

Sorry for my slow reply.

I followed these instructions to install the MIMIC-III database:
https://mimic.physionet.org/tutorials/install-mimic-locally-windows/

In the "Create the tables in the database" section they suggest using "CREATE SCHMEA mimiciii;" and then say "You will need to do this every time you launch psql".

The "options" passed to dbConnect deals with this mimiciii schema configuration. Without this option, the database is not found.