R Words Error: Failed to prepare query: Error: column "obs" does not exist

Question

I am trying to use WRDS with R by using this code:

I am getting this error:

I tried removing (obs=10) and I did not receive the same error, but when I entered:

The result was:

I tried this:

and got the same error.

Answer 1

I would say that approaches using dplyr/dbplyr are better when working with the WRDS PostgreSQL database.

To do the equivalent of (obs=10), do something like the following. The real payoff to dbplyr-based approaches comes when merging multiple large tables, which happens on the WRDS PostgreSQL server without needing to download data.

For many more examples like this one, see here.

library(dplyr, warn.conflicts = FALSE)
library(DBI)

pg <- dbConnect(RPostgres::Postgres())

djones.djdaily <- tbl(pg, sql("SELECT * FROM djones.djdaily"))

data <- 
  djones.djdaily %>%
  select(date, dji) %>%
  collect(n = 10)

data
#> # A tibble: 10 × 2
#>    date         dji
#>    <date>     <dbl>
#>  1 1896-05-26  40.9
#>  2 1896-05-27  40.6
#>  3 1896-05-28  40.2
#>  4 1896-05-29  40.6
#>  5 1896-06-01  40.6
#>  6 1896-06-02  40.0
#>  7 1896-06-03  39.8
#>  8 1896-06-04  39.9
#>  9 1896-06-05  40.3
#> 10 1896-06-08  39.8

^{Created on 2023-02-04 with reprex v2.0.2}

Answer 2

Try using dbFetch rather than fetch.

res <- dbSendQuery(wrds, "SELECT date, dji FROM djones.djdaily")

data <- dbFetch(res, n = -1)