RPostgreSQL and schemas

[This article was first published on R – scottishsnow, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
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",
               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")

To leave a comment for the author, please follow the link and comment on their blog: R – scottishsnow.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)