'Connecting R to postgreSQL database

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

sqlrpostgresql


Solution 1:[1]

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")

Solution 2:[2]

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)

Solution 3:[3]

One issue could be table permission

GRANT ALL PRIVILEGES ON your_table TO user

Replace your_table and user with your own credentials

You can get table from \dt and user from \du

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JackStat
Solution 2 bathyscapher
Solution 3 Kenan

Related Questions