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
8
votes
1 answer
Connect R to Redshift with RPostgreSQL on Mac 10.11.3
[Original Post]
I have seen a couple of similar SO questions but I don't think any of them are related to OS.
Long story short, this is what I see when I try to establish a ssl connection to Redshift.
library(RPostgreSQL)
drv <-…
Boxuan
- 4,937
- 6
- 37
- 73
8
votes
1 answer
Is it possible to read a data.table from PostgreSQL?
I'm doing some analysis on a large volume of data stored in a PostgreSQL database. For speed and memory reasons I'm using the data.table package. Currently I'm doing this to read the data.
library(RPostgreSQL)
library(data.table)
...
query <-…
rmccloskey
- 482
- 5
- 14
8
votes
2 answers
Is there a specific way to handle timestamp columns in R when pulling data using RPostgreSQL?
I'm trying to pull data from a PostgreSQL database and the results for a timestamp field are inconsistent. I'm not sure if I'm handling POSIXct results properly. Otherwise, I think I found a bug in the RPostgreSQL package. Here is the way to…
JAponte
- 1,508
- 1
- 13
- 21
7
votes
1 answer
Can't create dbConnect to Postgres with SSL
I'm running a Postgres-9.4 server that I would like to require SSL for. When I connect to the Postgres server from my laptop with either pgadmin or windows odbc connection, it works with SSL. However when I try to connect with R using SSL it…
Dean MacGregor
- 11,847
- 9
- 34
- 72
7
votes
2 answers
RPostgreSQL Cannot Close Connections
I have a shiny app that connects to a database using RPostgreSQL. At the end of the app the connection is closed and the driver should be unloaded but I get an error, warning me that the connection is not closed.
The code looks something like this:
…
David
- 9,216
- 4
- 45
- 78
6
votes
1 answer
How to use tidyr (or similar data wrangling) on "big" data in postgreSQL (Redshift) database
I have 4 billion rows of data in a 12 node redshift cluster. I am successfully able to connect to it with the Rpostgreqsql package and use dplyr to do basic data wrangling.
However, I'd like to do some data reshaping which I'd normally use reshape2…
leaRningR909
- 235
- 3
- 10
6
votes
1 answer
RPostgreSQL - import dataframe into a table
I want to export a complete dataframe into a table which is already created in a database(postgresql) and contains the similar data.
I found few questions explaining about dbwrite table (....overwrite = TRUE), i don't want to overwrite the data…
Chanti
- 525
- 1
- 5
- 15
6
votes
1 answer
RPostgreSQL connections are expired as soon as they are initiated with doParallel clusterEvalQ
I'm trying to setup a parallel task where each worker will need to make database queries. I'm trying to setup each worker with a connection as seen in this question but each time I try it returns for however…
Dean MacGregor
- 11,847
- 9
- 34
- 72
6
votes
0 answers
RPostgreSQL: datetime convert as date
I'm using the RPostgreSQL package to load data from a PostgreSQL data base.
The problem is that a datetime column (POSIXct) is automatically convert into a date.
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,…
user3904098
5
votes
1 answer
How to import data from PostgreSQL database to R?
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…
Mohamad Sahil
- 165
- 2
- 12
5
votes
1 answer
How to append/update a row from a data frame to a table in PostgreSQL db table that has the same columns using RPostgreSQL?
This question is an extension to the question already posted earlier in this forum. I need to append/update a row from a data frame to a table in PostgreSQL db table that has the same columns using RPostgreSQL.I am bale to copy the whole table or…
RanonKahn
- 853
- 10
- 34
5
votes
1 answer
Disconnect from PostgreSQL when close R Shiny App
This question is admittedly similar to this question but the answer provided is not sufficient for my example.
I am creating a Shiny App that connects to a database. The user can provide some inputs and some 'canned' queries will run. Now, I would…
cdeterman
- 19,630
- 7
- 76
- 100
5
votes
1 answer
How to execute sql query files via RPostgreSQL
I am accessing my PostgreSQL database (9.3) via R using the RPostgreSQL package.
I have a few very long and big sql queries (several MB big. generated from raster2pgsql).
How can I send / execute sql query files as statement within R?
The normal…
Curlew
- 1,022
- 18
- 39
5
votes
3 answers
Why can I not read my table although it is listed by dbListTables?
I am trying to read a table into R using RPostgreSQL and R v2.14.2.
My version of RPostgreSQL is listed as 0.3-2, downloaded 16-May-2012.
My version of DBI is listed as 0.2-5, downloaded 16-May-2012.
I can open the database, and list the tables. The…
Freda K
- 444
- 1
- 6
- 14
4
votes
1 answer
Improving RODBC-Postgres Write Performance
I've recently begun using RODBC to connect to PostgreSQL as I couldn't get RPostgreSQL to compile and run in Windows x64. I've found that read performance is similar between the two packages, but write performance is not. For example, using RODBC…
Jason B
- 893
- 7
- 13