[SOLVED] How to access and read Postgres views in R

How to access and read Postgres views in R

I am trying to access and read tables and views of Postgres database in R. I am able to get the tables using dbListTables function using RPostgres package but facing issues with views.

As having naive knowledge of postgres, looking for ways to access and read views as well in R.

library(RPostgres)
library(DBI)
library(dplyr)
library(sqldf)

pw<- {
"password"
}

conn <- dbConnect(RPostgres::Postgres()
             , host="host-name"
             , port='5432'
             , dbname="database-name"
             , user="username"
             , password=pw)

dbExistsTable(conn, "Test_Table")
#TRUE
dbListTables(conn)

mydf <- dbReadTable(conn, "Test_Table") # To Read the table in R

I have also tried the below command as per this link: https://github.com/tidyverse/dplyr/issues/1007 but no success.

SELECT table_name
FROM INFORMATION_SCHEMA.tables 
WHERE table_schema = ANY (current_schemas(false));

Solution

It seems that dbExistsTable and dbListTables cannot see Postgres views.

But you should be able to find them with a query like this one:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema not in ('pg_catalog', 'information_schema') and table_type = 'VIEW'

Once you know the name of the view you're looking for, dbReadTable(conn, "myview") works.

Note: if it still doesn't work, make sure to set the right schema using

SET search_path to myschema