215 questions
0
votes
1
answer
74
views
Foreign key constraint lost in PostgreSQL table in Azure when data is added using R Script
I have a table in PostgreSQL database, hosted on Azure cloud. In this table, I have created foreign key constraints on multiiple columns.
I have a script in R which tries to push the contents of a ...
- 19
0
votes
1
answer
167
views
Unable to install RPostgres package in Docker container
I am new to the concept of containerising R shiny apps using Docker and I was trying to deploy my R shiny application on Azure cloud environment by deploying a docker container image. This R shiny app ...
- 19
0
votes
0
answers
19
views
RPostgresql executing a function that returns void
I have a code that looks like this:
con <- connect_to_my_postgres_database()
dbClearResult(dbExecute(con, "select function_that_returns_void()")
postgresqlCloseConnection(con)
This is ...
- 1,176
0
votes
0
answers
53
views
Why is my query returning varying results, and how do I go about troubleshooting this issue?
When I run a simple count(*) using dbplyr, I get anywhere from 40k to 70k at a result:
> tbl(con,sql("select count(*) from app_public.members"))
# Source: SQL [1 x 1]
# Database: ...
- 382
0
votes
1
answer
29
views
Update only certain columns in SQL parent table from R
Let's say I have mtcars in a postgres server.
library("RODBC")
library("odbc")
library("RPostgres")
con = dbConnect(RPostgres::Postgres(),dbname="example_db",...
- 3,689
1
vote
1
answer
116
views
Passing postgresql parameter keywords via RPostgreSQL
I would like to pass some parameter keywords (specifically keepalives, keepalives_idle, keepalives_count , or connect_timeout) using the R package RPostgreQSL to connect to my DB.
I have searched the ...
- 199
0
votes
2
answers
171
views
How to import/copy different csv files into 1 table in SQL using R?
I have multiple csv files (1 file for each city) having following structure.
An example of two out of many files:
CSV File 1:
City_Name Pop PopDen Lit
MI 45000 280 78
MI 37000 310 89
...
...
...
- 51
1
vote
0
answers
202
views
Cannot connect to Postgres database from RStudio but can connect from DBeaver and psql
Can connect to the database from psql and DBeaver but when using RpostgreSQL package in RSudio I receive the following error:
RPosgreSQL error: could not connect user@hostname on dbname "database&...
- 11
7
votes
2
answers
2k
views
RPostgreSQL - SCRAM error when trying to connect to local database
I am trying to connect to my localhost postgres DB and I get the following error.
library("RPostgreSQL")
drv <- dbDriver("PostgreSQL")
connec <- dbConnect(drv, dbname = "...
- 3,689
1
vote
0
answers
789
views
Handling passwords in R and Shiny app in .Renviron file as a secure production environment
Why is keeping your password, port, host, and all secrets of a database (e.g. PostgreSQL) in the .Renviron file safe for Shiny apps and R scripts?
I have a question about the .Renviron file and its ...
1
vote
0
answers
157
views
Incomplete error message from RPostgreSQL
The error messages I get from RPostgreSQL contain nonsense characters where there should be some informative text. Here are two examples:
Example 1: Querying a non-existent table
> library("...
- 13.7k
0
votes
1
answer
84
views
Normalizing data in R - dataframe construction
I'm using R to connect to a database I have stored in PGAdmin so that I can add data into the database through R. Before adding the data into the database, it has to be normalized.
The dataset ...
- 195
1
vote
1
answer
34
views
Batch executing query in RPostgres returning same data
I am currently trying to download a large database using RPostgres - however, when I try to write the results to csvs in batches, it writes the same data over and over (always the first 1M records). ...
- 11
2
votes
1
answer
240
views
Time zone error when using RPostgres to connect to Azure database
I've been having trouble connecting to an Azure Postgres database via RPostgres using RStudio.
When I try to connect using
dbConnect(RPostgres::Postgres(), dbname = db, host=host_db, port=db_port, ...
- 31
1
vote
1
answer
465
views
Problem building R api with plumber, RPostgreSQL, and docker
I'm trying to install plumber and RPostgreSQL into my docker image. Here's my dockerFile:
FROM rocker/r-base
RUN R -e "install.packages('plumber')"
RUN R -e "install.packages('...
2
votes
2
answers
455
views
Insert R list into RPostgreSQL query
I'm running a postgreSQL query based on an automated list of ID's stored in an R list. I'm trying to determine how to include that R list in my query so I don't have to hard-code the ID's each time I ...
- 57
2
votes
0
answers
579
views
R Fatal error with RPostgreSQL + R + macOS Monterey
I am trying to connect to redshift database using R.
Since I upgraded my mac to Monterey I receive a message in R as
" R Session Aborted -R encountered a fatal error - the session was terminated&...
- 21
2
votes
0
answers
495
views
dbConnect to Postgres gives strange error not allowing me to connect
I am trying to connect to my localhost Postgres DB within R to further read/write tables. The server is running in the background which I'm monitoring in the background. I'm using the RPostgreSQL ...
- 97
10
votes
2
answers
992
views
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 ...
- 5,129
0
votes
1
answer
318
views
Using RpostgreSQL with sqldf crashes R
I am using sqldf to train some r-users in SQL. Loading RPostgreSQL or RH2 before loading sqldf will change the default SQL used by sqldf. This works fine with h2, but every time I load RPostgreSQL, R ...
- 3,836
0
votes
2
answers
146
views
Similar aggregate function to unique/distinct in R Postgres Backend
How does it work to aggregate a variable in a postgres db backend table to its unique value?
For example, I have the following table:
library(tidyverse)
library(dbplyr)
dbplyr::memdb_frame(a=c(2,2,2),...
- 149
0
votes
1
answer
125
views
WHERE Statement Throwing Error using VARCHAR [duplicate]
I am using the DBI package with the RPostgreSQL driver to query from an amazon redshift database and am able to pull tables with simple queries without issue. By simple queries I mean something like ...
- 15
1
vote
1
answer
123
views
dplyr: use a custom function in summarize() when connected to external database
Is there a way to use custom functions within a summaries statement when using dplyr to pull data from an external database?
I can’t make usable dummy data because this is specific to databases, but ...
- 62
2
votes
2
answers
742
views
Import dump file into PostgreSQL through RPostgreSQL
I'm wondering if there's a way to import PostgreSQL dump through R (RPostgreSQL) into a PostgreSQL database.
I understand this is the way we write csv or a datatable from R into PostgreSQL through ...
- 1,664
1
vote
0
answers
237
views
RPostgres on Mac fails to connect to Amazon RDS Aurora database
I am facing an awkward issue connecting to a Postgres database deployed on AWS Aurora. Connection attempts from R on Mac OS, using the DBI and RPostgres frameworks are rejected with the message:
...
2
votes
1
answer
884
views
Unable to find an inherited method for function 'dbClearResult' for signature '"data.frame"'
I have a problem with my R program querying a PostgreSQL database. Here is the code
### Here we assume that myuser, mypassword, db, host_db and db_port
### have been defined in previous lines in the ...
- 1,471
1
vote
1
answer
377
views
How to transform bytea column containing RDS binary format in dataframe?
I need to extract data from a BYTEA field of a postgeSQL database. In the BYTEA field, the data is stored in the RDS format (it is the contents of a .rds file created by the command saveRDS ...
- 21
0
votes
1
answer
2k
views
Creating primary key in postgresql takes days
I am trying to write my table to a PostgreSQL database then declare a primary key. My data is really huge (includes billions of rows and total size is approximately 150 GB), when I try to create a ...
- 2,730
2
votes
2
answers
1k
views
Subtracting 1 Year from Date without Lubridate
I have a dataframe in a database with a date dimension that I pulled using RPostgres. Each 'date' is in the format 'YYYY-MM-DD. I'd like to add a new column of dates (labeled 'lookback_date') that are ...
- 53
1
vote
1
answer
1k
views
How to append a new column to a postgresql table from a dataframe from R
I am trying to append or cbind a new column from R to a postgresql table. So, I have created a new column in the postgresql table where I want to put my dataframe column, i.e
ALTER TABLE schema.table
...
1
vote
0
answers
237
views
R: NULL value passed as symbol address, error in .Call(RS_PostgreSQL_init, config.params, force) :
I'm new to stackoverflow, so please forgive me if this is not the right place for my question.
When I set up a connectin with PostgreSQL via RPostgreSQL package i get the following error:
error in ....
- 19
0
votes
1
answer
253
views
Error in connecting to PostgreSQL in R using DBI connector
i am facing the following challenge, when i am trying to connect to the Rpostgresql using the DBI package i am getting the following error.
Below is the code used for my connections
install.packages(&...
2
votes
2
answers
11k
views
Error connecting to postgres 13 using RPostgreSQL package in R. "authentication method 10 not supported"
I am attempting to connect to a local postgres 13 server using the RPostgreSQL package in R studio. I am receiving the error below. I am able to connect to a remote postgres 9 server using the exact ...
- 361
2
votes
2
answers
764
views
R connect to postgres return false in dbExistsTable, but it is wrong
I try connect to my table in postgres.
Here screen of my db.
So i need dbo.social and table profiles.
So to do this, i try
library(RPostgres)
library(DBI)
pw<- {
"1234"
}
con <- ...
- 3,205
0
votes
0
answers
91
views
Reading data from edbpostgres into R (date problem)
I have a problem with importing data from edb postgres into R (with RPostgres). I using this code:
library(RPostgres)
conn <- dbConnect(Postgres(), dbname = 'xx', host = 'xx', port = 'xx',
...
- 97
1
vote
1
answer
70
views
drob tables their names begin with numbers in postgress using RPostgreSQL
I try to drop tables in an scheme their names begin with digits from R. The tables exist in a postgress data bank.
library(RPostgreSQL)
library(DBI)
pg = dbDriver("PostgreSQL")
con = ...
- 1,469
1
vote
2
answers
269
views
R Shiny wants to hide the global.R password
I am writing an application in Shiny in R. Global.R is described as below to connect to PostgresSQL.
library(shiny)
library(DBI)
library(RPostgres)
library(RPostgreSQL)
library(pool)
pool <- ...
- 231
0
votes
1
answer
152
views
RPostgreSQL query not working in Shiny server
I am trying to get data from a PostgreSQL database in a shiny app. The query is not working. Running the following
function(input, output) {
data.df<- eventReactive(input$fetcher, {
qry<-...
- 76
1
vote
0
answers
156
views
Matching postegresSQL foreign key with the primary key using R
I've 2 tables in my postegreSQL database
CREATE TABLE touriste (
idclient BIGSERIAL PRIMARY KEY,
numclient INT,
nameclient VARCHAR(500),
codepost INT,
departement VARCHAR(500),
pays VARCHAR(100)
);
...
0
votes
0
answers
840
views
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 ...
- 41
0
votes
0
answers
783
views
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....
- 35
2
votes
2
answers
559
views
Import multiple csv files into postgresql database using r (memory error)
I am trying to import a dataset (with many csv files) into r and afterwards write the data into a table in a postgresql database.
I successfully connected to the database, created a loop to import ...
- 37
7
votes
2
answers
4k
views
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) :
...
- 109
0
votes
1
answer
158
views
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 ...
- 29
0
votes
0
answers
356
views
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 ...
- 2,111
0
votes
0
answers
351
views
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 = "...
- 1
0
votes
1
answer
107
views
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',
...
- 758
0
votes
1
answer
760
views
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")
...
- 185
0
votes
0
answers
128
views
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 ...
- 1
0
votes
0
answers
73
views
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....
- 20k