<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/rpostgresql-and-schemas/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://scottishsnow.wordpress.com/2017/05/01/rpostgresql-schemas/"> R – scottishsnow</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The database PostgreSQL can have different <a target="_blank" rel="nofollow" href="https://www.postgresql.org/docs/current/static/ddl-schemas.html">schemas</a>. These work like a window for users, where they get to see specific things within a database, e.g. tables.</p>
<p>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:</p>
<pre>
`Error in `[.data.frame`(dbGetQuery(conn, paste(&quot;select a.attname from pg_attribute a, pg_class c, pg_tables t, pg_namespace nsp&quot;,  :
undefined columns selected`
</pre>
<p>or:</p>
<pre>
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR:  relation &quot;locations&quot; does not exist
LINE 1: SELECT * FROM locations
^
)
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
Could not create executeSELECT * FROM locations`
</pre>
<p>As far as I’m aware, you can’t connect to a db from R and specify a schema (<a target="_blank" rel="nofollow" href="http://stackoverflow.com/questions/42139964/setting-the-schema-name-in-postgres-using-r">watch this</a>), but you can specify the schema when you’re connected. Here’s some code to get you connected:</p>
<pre>
library(RPostgreSQL)

db = dbConnect(PostgreSQL(),
               user=&quot;name&quot;,
               password=&quot;pwd&quot;,
               host=&quot;some.where.com&quot;,
               port=5432,
               dbname=&quot;mydb&quot;)
</pre>
<p>The usual <code>dbListTables(db)</code> 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:</p>
<pre>
# List tables associated with a specifc schema
dbGetQuery(db,
           &quot;SELECT table_name FROM information_schema.tables
                   WHERE table_schema='sch2014'&quot;)

# List fields in a table
dbListFields(db, c(&quot;sch2014&quot;, &quot;tablename&quot;))

# Query your database
x = dbGetQuery(db, &quot;SELECT * FROM sch2014.tablename&quot;)
</pre>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/scottishsnow.wordpress.com/1185/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/scottishsnow.wordpress.com/1185/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://scottishsnow.wordpress.com/2017/05/01/rpostgresql-schemas/"> R – scottishsnow</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/_WxZxoG0L6o"></a></div>