How to import data from PostgreSQL database to R?

Question

I'm thinking of importing data from database directly into r using RPostgresQL package. So far, I used to write queries in Postico (a PostgreSQL client) software and export as csv and then import the csv file into R.
This is what I've written so far and no clue how to proceed next.

library('RPostgreSQL')
pg=dbDriver("PostgreSQL")
con = dbConnect(pg, user="msahil515", password="",
            host="localhost", port=5432, dbname="msahil515")

How do I load tables from the database into R after this or how to write queries in R to extract only necessary data from database?

?dbSendQuery / ?dbGetQuery I mean, you have to read R manual pages at some point, right? — hrbrmstr
– hrbrmstr, Commented Nov 6, 2018 at 18:38
@hrbrmstr Yeah, I tried with dbWriteTable(con,"some_table_name",table_name,row.names=F) but I get an error that the object table_name not found. — Mohamad Sahil
– Mohamad Sahil, Commented Nov 6, 2018 at 18:54
Why are you using dbWriteTable() when your question says you want to load tables and extract data? — hrbrmstr
– hrbrmstr, Commented Nov 6, 2018 at 19:24
Sorry, my bad! I wasn't even sure if R was connected to the correct database. dbGetQuery() worked for me. Thanks! — Mohamad Sahil
– Mohamad Sahil, Commented Nov 6, 2018 at 19:50

x85ms16 · Accepted Answer · 2020-07-20 16:56:24Z

7

Here is a straight answer to your question. This definitely can be extended

library('RPostgreSQL')

#create connection object
con <- dbConnect(drv =PostgreSQL(), 
                 user="msahil515", 
                 password="",
                 host="localhost", 
                 port=5432, 
                 dbname="msahil515")

dbListTables(con)   #list all the tables 

#query the database and store the data in datafame
first_results <- dbGetQuery(con, "SELECT * from FOO limit 10;")

dbDisconnect(con)   #disconnect from database

edited Jul 20, 2020 at 16:56

answered Feb 15, 2019 at 19:03

x85ms16

6358 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

x85ms16 Over a year ago

Also, i recommend writing your credintials in .Renviron or .Rprofile instead of hardcoding on your credentials on the scripts.

cbo Over a year ago

This answer was close but did not work for me : you need to change the driver name to "PostgreSQL" (as mentioned in the question, this might be a typo).

Collectives™ on Stack Overflow

How to import data from PostgreSQL database to R?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related