RPostgreSQL and schemas

May 1, 2017
By

(This article was first published on R – scottishsnow, and kindly contributed to R-bloggers)

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 on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


Sponsors

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)