Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Connecting R to postgreSQL database

Tags:

sql

r

postgresql

I am trying to connect R to a postgreSQL database. He is what I have been trying in R:

require("RPostgreSQL")

pw<- {
  "password"
}

# loads the PostgreSQL driver
drv <- dbDriver("PostgreSQL")
# creates a connection to the postgres database
# note that "con" will be used later in each connection to the database
con <- dbConnect(drv, dbname = "DBname",
                 host = "localhost", port = 5432,
                 user = "user", password = pw)
rm(pw) # removes the password

# check for the test_table
dbExistsTable(con, "test_table")
# FALSE >>> Should be true

I cannot figure out why it is not properly connecting to my database. I know that the database is on my computer as I can connect to it in the terminal and with pgAdmin4. Any help is greatly appreciated.

Thanks

like image 392
erik12324 Avatar asked Nov 16 '16 21:11

erik12324


2 Answers

I have had better success with the RPostgres package in combination with DBI and I know that RPostgreSQL just released a new version in May after no changes for a while. RPostgres is pretty active

## install.packages("devtools")
#devtools::install_github("RcppCore/Rcpp")
#devtools::install_github("rstats-db/DBI")
#devtools::install_github("rstats-db/RPostgres")

library(RPostgres)
library(DBI)

pw<- {
  "password"
}

con <- dbConnect(RPostgres::Postgres()
     , host='localhost'
     , port='5432'
     , dbname='DBname'
     , user='user'
     , password=pw)


rm(pw) # removes the password

dbExistsTable(con, "test_table")
like image 157
JackStat Avatar answered Oct 16 '22 16:10

JackStat


install.packages("RPostgreSQL")
require("RPostgreSQL")
# this completes installing packages

# now start creating connection
con <- dbConnect(dbDriver("PostgreSQL"),
                 dbname = "dbname",
                 host = "localhost",
                 port = 5432,
                 user = "db_user",
                 password = "db_password")
# this completes creating connection

# get all the tables from connection
dbListTables(con)
like image 21
Venkat Avatar answered Oct 16 '22 16:10

Venkat