RPostgreSQL and schemas

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",
               password="pwd",
               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"))

# Query your database
x = dbGetQuery(db, "SELECT * FROM sch2014.tablename")