How to import data from PostgreSQL database to R?

4.8k views Asked by At

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?

1

There are 1 answers

2
x85ms16 On BEST ANSWER

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