An R interface to the PostgreSQL database system
RPostgreSQL is an r package providing an interface to postgresql databases. RPostgreSQL uses the r-dbi interface which is also used by the packages rsqlite, rmysql, rjdbc, and roracle.
Questions tagged [rpostgresql]
210 questions
4
votes
1 answer
Queries in RPostgreSQL are very slow
Currently I'm building a Shiny APP using several queries to a PostgreSQL database (mainly SELECT and INSERT statements). The application works but I'm trying to make it faster. When I compare the execution times between the same query using the…
Esteban Angel
- 51
- 2
- 4
4
votes
1 answer
View a list of all schema in a PostgreSQL database in r?
I'm connecting and querying a PostgreSQL database through the dplyr package in R. I know I can list all the tables in the database using dbListTables(con).
But, my tables are organized into different schema. What is the easiest way I can view all of…
T. Shiftlet
- 158
- 13
4
votes
1 answer
unable to install RPostgreSQL in rstudio-server docker container to connect to postgres
I have a rstudio-server docker container (R-server) and a postgres docker container (Pg), and am using rstudio-server to connect to postgres database via RPostgreSQL package. However, I can't install RPostgreSQL package on R-server, and don't want…
monca
- 41
- 3
4
votes
2 answers
How can I append data to a PostgreSQL table with `dplyr` without `collect()`?
The table reg_data is a PostgreSQL table. It turns out to be faster to run the regressions in PostgreSQL. But, as I am running it for 100,000s of data sets, I want to do it data set by data set and append the results of each to a table.
Is there a…
Ian Gow
- 3,098
- 1
- 25
- 31
4
votes
2 answers
How to retrieve more than 100000 rows from Redshift using R and dplyr
I'm analyzing data from a Redshift database, working in R using a connection per dplyr - which works:
my_db<-src_postgres(host='my-cluster-blahblah.redshift.amazonaws.com', port='5439', dbname='dev',user='me', password='mypw')
mytable <- tbl(my_db,…
Lucy
- 51
- 6
4
votes
1 answer
RPostgreSQL - R Connection to Amazon Redshift - How to WRITE/Post Bigger Data Sets
I'm experimenting with how to connect R with Amazon's Redshift - and publishing a short blog for other newbies.
Some good progress - I'm able to do most things (create tables, select data, and even sqlSave or dbSendQuery 'line by line' HOWEVER, I…
Ryan Anderson
- 91
- 1
- 9
4
votes
2 answers
How to send application_name to RPostgreSQL connection
I know that PostgreSQL database allows to keep track of application name for each connection and it looks like application_name variable should work with RPostgreSQL, but how exactly should I do this? Adding variable application_name = "test" to…
kismsu
- 1,049
- 7
- 22
4
votes
2 answers
RPostgreSQL installation Error - RPostgreSQL.so: undefined symbol: PQpass
I am trying to install R-Package RPostgreSQL, but getting the following error,
Error in dyn.load(file, DLLpath = DLLpath, ...) :
unable to load shared object '/usr/lib64/R/library/RPostgreSQL/libs/RPostgreSQL.so':
…
Manoj G
- 1,776
- 2
- 24
- 29
4
votes
4 answers
Problems connecting remotely to PostgreSQL on Heroku from R using RPostgreSQL
I'm using the RPostgreSQL 0.4 library (compiled on R 2.15.3) on R 2.15.2 under Windows 7 64-bit to interface to PostgreSQL. This works fine when connecting to my PostgreSQL databases on localhost. I'm trying to get my R code to run with a remote…
user2252523
- 41
- 4
4
votes
3 answers
RPostgreSQL access to database, error: unable to find an inherited method for function "show", for signature "PostgreSQLConnection"
This is a new one, and I have not found any mentions of this on other stackoverflow sites.
So I am programming in R and using the RPostgreSQL package to access a Postgres 9.1.5 database. So I created the connection to the database as I would…
krishnab
- 9,270
- 12
- 66
- 123
3
votes
2 answers
How do i fix the warning message "Closing open result set, cancelling previous query" when querying a PostgreSQL database in R?
Below is a snippet of my code that I use in R to extract IDs from a PostgreSQL database. When I run the function I get the following warning message from R:
In result_create(conn@ptr, statement) :
Closing open result set, cancelling previous…
cnburger
- 59
- 1
- 8
3
votes
1 answer
Insert/Update R data.table into PostgreSQL table
I have a PostgreSQL database set up with a table and columns already defined. The primary key for the table is a combination of (Id, datetime) column. I need to periodically INSERT data for different Ids from R data.table into the database. However,…
ishan
- 150
- 13
3
votes
2 answers
how to use parameterized query using R and Rpostgres so as to accept values contained in a variable as input to the psql
I want to pass values contained in variables as inputs to a postgresql query using R.
Currently I'm following where it gives as example:
https://db.rstudio.com/best-practices/run-queries-safely/
Using a parameterised query with DBI requires three…
kRazzy R
- 1,561
- 1
- 16
- 44
3
votes
0 answers
Can RPostgreSQL run parameterized SQL statements with named parameters?
I have tried several times to run parameterized SQL statements using the RPostgreSQL package in R. I was able to run parameterized statements using positional arguments, however, I have had no luck for named arguments. Is this possible in…
ichbinallen
- 1,019
- 12
- 18
3
votes
1 answer
Case-sensitivity with dplyr + RPostgreSQL string matching
I can't figure out how to apply a non-case-sensitive filter query to a remote PostgreSQL table using dplyr. To demonstrate:
require(dplyr)
require(stringr)
require(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="mydb",…
geotheory
- 22,624
- 29
- 119
- 196