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
22
votes
5 answers
UTF-8 / Unicode Text Encoding with RPostgreSQL
I'm running R on a Windows machine which is directly linked to a PostgreSQL database. I'm not using RODBC. My database is encoded in UTF-8 as confirmed by the following R command:
dbGetQuery(con, "SHOW CLIENT_ENCODING")
# client_encoding
# 1 …
David L
- 223
- 1
- 2
- 5
19
votes
3 answers
I cannot connect postgresql schema.table with dplyr package
Im trying to connect postgres with dplyr functions
my_db <- src_postgres(dbname = 'mdb1252', user = "diego", password = "pass")
my_db
src: postgres 9.2.5 [postgres@localhost:5432/mdb1252]
tbls: alf, alturas, asociad, atenmed, base, bfa_boys_p_exp,…
Diego
- 2,196
- 1
- 21
- 26
15
votes
2 answers
Using sqldf and RPostgreSQL together
When using RPostgreSQL I find that I cannot use sqldf in the same way. For example if I load the library and read in data into a data frame using the following code:
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,…
djq
- 14,810
- 45
- 122
- 157
13
votes
2 answers
Install RPostgreSQL on RHEL 6.5 libpq-fe.h Error
I have RHEL 6.5 Server with an installation of R (3.1.1) & RStudioServer (0.98.1062)
I have postgresql-9.3 installed and handling a large database. In order to connect R to PostgreSQL, I have in the past used the RPostgreSQL (still do on my CentOS 7…
Serban Tanasa
- 3,592
- 2
- 23
- 45
12
votes
1 answer
Can I run an SQL update statement using only dplyr syntax in R
I need to update column values conditionnaly on other columns in some PostgreSQL database table. I managed to do it writing an SQL statement in R and executing it with dbExecute from DBI package.
library(dplyr)
library(DBI)
# Establish connection…
Romain
- 1,931
- 1
- 13
- 24
11
votes
1 answer
Installing RPostgreSQL on Linux
When I try on linux in R
install.packages('RPostgreSQL')
it fails with unspecified errors:
Warning message:
In install.packages("RPostgreSQL") :
installation of package ‘RPostgreSQL’ had non-zero exit status
I tried to solve problem…
Marta
- 3,493
- 6
- 28
- 43
11
votes
1 answer
How to use column defaults with dbWriteTable(..., append=TRUE) in RPostgreSQL
The dbWriteTable function in RPostgreSQL seems to ignore column names and tries to push data from R to PostgreSQL as-is. This is problematic when appending to existing tables, particularly if there are columns un-specified in the R object that…
Zach
- 29,791
- 35
- 142
- 201
11
votes
3 answers
How to use parameters with RPostgreSQL (to insert data)
I'm trying to insert data into a pre-existing PostgreSQL table using RPostgreSQL and I can't figure out the syntax for SQL parameters (prepared statements).
E.g. suppose I want to do the following
insert into mytable (a,b,c) values ($1,$2,$3)
How do…
Corvus
- 7,548
- 9
- 42
- 68
9
votes
1 answer
Read/write Postgres large objects with DBI & RPostgres
I'm trying to use the large object (https://www.postgresql.org/docs/10/largeobjects.html) feature of PostgreSQL in R, and I have some trouble writing and reading using {DBI}/{RPostgres}.
Here is what I have tried so far:
# Getting the db
docker run…
Colin FAY
- 4,849
- 1
- 12
- 29
9
votes
2 answers
Reconnect to PostgreSQL database with R's pool package
I have an API built with R plumber that connects to a PostgreSQL database using RPostgreSQL and pool (although this would also apply if I was using a Shiny app):
# create the connection pool
pool <- dbPool(
drv = PostgreSQL(),
host =…
Devin
- 851
- 12
- 32
9
votes
2 answers
How can I compile RpostgreSQL with libssl and libpg and SSL activation
I am using R on Windows to connect to a PostgreSQL database hosted on AWS. The database is set up using forcessl = 1 - this means that any connection needs to be set up with sslmode=require.
The base RPostgreSQL package does not provide any exposure…
Chris
- 6,302
- 1
- 27
- 54
9
votes
1 answer
R RPostgreSQL Connect to Remote Postgres Database with SSL
I am trying to connect to a remote PostgreSQL database from within R using the RPostgreSQL package, and I am getting errors that appear to be related to the SSL settings for the connection. I have verified that I can connect from the command line…
bmosov01
- 589
- 5
- 15
9
votes
2 answers
Proper way to pass parameters to query in R DBI
In perl/python DBI APIs have a mechanism to safely interpolate in parameters to an sql query. For example in python I would do:
cursor.execute("SELECT * FROM table WHERE value > ?", (5,))
Where the second parameter to the execute method is a…
cts
- 1,790
- 1
- 13
- 27
8
votes
1 answer
R and RStudio not detecting installed PostgreSQL (or any other) ODBC drivers
I am trying to connect to a PostgreSQL database using the R ODBC drivers in RStudio. For some reason, the drivers are not being detected by R:
$ Rscript -e 'odbc::odbcListDrivers()'
[1] name attribute value
<0 rows> (or 0-length…
Floris
- 637
- 1
- 8
- 17
8
votes
3 answers
Does PostgreSQL numeric type support infinity (and -infinity)?
Following on from this question and subsequent answers:
Appropriate values for -Infinity & Infinity in Postgres
and the documentation, it seems clear that the real and double precision number types support both positive and negative infinity.…
Alex
- 15,186
- 15
- 73
- 127