Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

The database PostgreSQL can have different schemas. These work like a window for users, where they get to see specific things within a database, e.g. tables.

In this post we’ll look at how we can access a database with a specific schema. You’ll know you need this page if you get errors like:

Error in [.data.frame(dbGetQuery(conn, paste("select a.attname from pg_attribute a, pg_class c, pg_tables t, pg_namespace nsp",  :
undefined columns selected


or:

Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR:  relation "locations" does not exist
LINE 1: SELECT * FROM locations
^
)
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
Could not create executeSELECT * FROM locations


As far as I’m aware, you can’t connect to a db from R and specify a schema (watch this), but you can specify the schema when you’re connected. Here’s some code to get you connected:

library(RPostgreSQL)

db = dbConnect(PostgreSQL(),
user="name",
host="some.where.com",
port=5432,
dbname="mydb")


The usual dbListTables(db) returns all tables within a db, but many of these you either can’t access or would not want to access. So here are some lines to work with your schema on a database:

# List tables associated with a specifc schema
dbGetQuery(db,
"SELECT table_name FROM information_schema.tables
WHERE table_schema='sch2014'")

# List fields in a table
dbListFields(db, c("sch2014", "tablename"))

`