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
0
votes
0 answers
Retain the text values with special character (such as hyphen and space) when updating to PostgreSQL database through R
I want to update a table in PostgreSQL table from a newData dataframe in local through a loop when the id matches in both tables. However, I encountered issues that the text values do not update exactly as our newData to the database. Number is…
chichi
- 31
- 1
- 4
0
votes
0 answers
How do I connect to postgres database in Rstudio (MacOS)?
I'm trying to connect from RStudio on my Mac to a PostgreSQL database.
The following code chunk works successfully on the Windows and Mac RStudio installs of my colleagues.
blah.blah.rds.amazonaws.comblah.blah.rds.amazonaws.com"
dbport <-…
N.A.K
- 35
- 6
0
votes
1 answer
Unable to write dataframe in R as Update-Statement to Postgis/PostgresSQL
I have the following dataframe:
library(rpostgis)
library(RPostgreSQL)
library(glue)
df<-data.frame(elevation=c(450,900),
id=c(1,2))
Now I try to upload this to a table in my PostgreSQL/Postgis database. My connection (dbConnect) is…
Johannes42
- 29
- 4
0
votes
0 answers
open ssh tunnel in R script
I connect to a PostgreSQL DB via RPostgres to perform queries.
My current workflow is the following:
In the terminal, I create a tunnel via ssh.
ssh -L PORT:host:25060
On top of my R script, I create the DB connection:
# use RPostgres…
chopin_is_the_best
- 1,951
- 2
- 23
- 39
0
votes
0 answers
How to connect to a Postgres database with a ssh tunnel with R?
at work we have a database to which the following series of commands connected me from R.
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- DBI::dbConnect(
drv = drv,
host = "rocket.cluster-east-1.rds.amazonaws.com",
user = "…
Fernando
- 1
- 1
0
votes
1 answer
How to write tables into Panoply using RPostgreSQL?
I am trying to write a table into my data warehouse using the RPostgreSQL package
library(DBI)
library(RPostgreSQL)
pano = dbConnect(dbDriver("PostgreSQL"),
host = 'db.panoply.io',
port = '5439',
…
Jacky
- 710
- 2
- 8
- 27
0
votes
1 answer
Permission denied to write a table with dbWriteTable in RPostgreSQL
I've been trying to solve a problem with the RPostgreSQL package for quite some time. My code:
path_ene <- "C:/Users/omen03/ENE"
nene <- "ene_2010_02_efm.csv"
drv <- dbDriver("PostgreSQL")
con = dbConnect(drv, user="postgres",…
Héctor Garrido
- 185
- 1
- 14
0
votes
0 answers
How to display text message from Postgresql function in R shiny
Im currently working on my shiny app, and what im dealing with now is viewing return message from SQL function on shiny app, after using this function.
Here is my function:
CREATE OR REPLACE FUNCTION delivery(s_i INTEGER, n INTEGER, q INTEGER,…
tadejow
- 1
- 1
0
votes
0 answers
How to use parameterized IN with RPostgreSQL
I have the following query:
query <- 'SELECT field1, field2
from mytable
where field1 IN $1
AND field2 <= $2
ORDER BY field1 ASC, field2 DESC;'
params <- list(list('A','B'), as.character(as.POSIXlt(Sys.time(), tz="UTC")))
print(params)
I run:
df…
Mittenchops
- 18,633
- 33
- 128
- 246
0
votes
1 answer
R Studio Redshift Connection Over VPN
I am attempting to connect to Redshift using the RPostgres package.
Normally this works fine, however I am using VPN to connect to my companies network.
Running the following, with placeholders for sensitive info obviously:
library(RPostgres)
con…
Devin
- 363
- 2
- 20
0
votes
1 answer
Dates math does not result in same results in R vs. Redshift
I am having an issue in using the RPostgres package to connect with Redshift. I am unsure if it is an issue with our database set up, or if it is a known issue with the package.
I am getting different results when I use the same exact query in…
Devin
- 363
- 2
- 20
0
votes
1 answer
What operations can SQL perform that R packages which wrap SQL operations can’t?
For example, if I’m presented with a PostgreSQL database, what am I missing out on by using RPostgreSQL to interface with it instead of executing SQL operations using the command line or a database management application? Are there some things that…
JohnDoeVsJoeSchmoe
- 671
- 2
- 8
- 25
0
votes
2 answers
Connecting Rstudio to PostgresSQL
I am trying to connect to a external database (PostgresSQL) from Rstudio by using DBI and RPostgres. I defined all parameters:
con <- DBI::dbConnect(RPostgres::Postgres(),
dbname = 'name',
host =…
Gabriel Teotonio
- 33
- 2
- 4
0
votes
0 answers
Passing R data.table column values to udf input argument in postgres
I have a udf in postgres that expects array as input argument. Example:
create or replace function vwap(prices numeric[], volume integer[])
I have the prices and volume stored in a R data.table.
dt <- data.table(prices = numeric(), volume =…
aajkaltak
- 1,437
- 4
- 20
- 28
0
votes
2 answers
dplyr:: append to postgresql remote source if uid not present
Unless I'm missing something there is no reference in documentation to how to use functions such as db_write_table in practice. The only reference in package documentation is.
db_write_table(con, table, types, values, temporary = FALSE, ...)
types…
geotheory
- 22,624
- 29
- 119
- 196