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
1
vote
1 answer
How to set a RpostgreSQL query as a dataframe object for RpivotTable?
I am trying to use RpostgreSQL to source data to be fed into the rpivotTable package and displayed to the user using shiny.
Error: data should be a data.frame, data.table, or table
Unfortunately I am new to R and I am unable to work out how to set…
Brr
- 23
- 4
1
vote
0 answers
Using RPostgreSQL and INSERT INTO - Do I need to bind my dataframe?
I'm writing a query using RPostgreSQL meant to fill a table one time. I really have no intention to do anything else with the data within R. I just need it for is to run the function to fill that table.
library(data.table)
library (RPostgreSQL)
…
user9302275
1
vote
0 answers
Why does dbGetQuery take so long when the number of columns is large?
I am baffled why a simple query takes so long when executed through RPostgreSQL but completes almost instantly through another client. I have a Postgresql database ncol = 500 and nrow = 100. I would like to read the whole database into a dataframe…
ichbinallen
- 1,019
- 12
- 18
1
vote
1 answer
Is it possible to run Postgres backslash commands via RPostgresql?
Postgres has many convenient "backslash" commands (e.g \dt, \du, \l, etc). I would like to run these via the RPostgresql interface. I have tried the following:
drv = dbDriver("PostgreSQL")
con <- dbConnect(drv,
dbname =…
ichbinallen
- 1,019
- 12
- 18
1
vote
1 answer
RPostgreSQL - Query a table's primary key based on Vector of primary keys
Question: How can RPostgreSQL be used to query a database table based on a variable containing a vector of primary keys?
Example: In this I have the following code which works for one instance, but does not work when I try to vectorize…
Daniel
- 83
- 1
- 1
- 10
1
vote
0 answers
RSQLite dbWriteTable not working on large data
Here is my code, where I am trying to write a data from R to SQLite database file.
library(DBI)
library(RSQLite)
library(dplyr)
library(data.table)
con <- dbConnect(RSQLite::SQLite(), "data.sqlite")
### Read the file you want to load to the…
Geet
- 2,515
- 2
- 19
- 42
1
vote
0 answers
How do I connect from R to Postgres without getting: "FATAL: Ident authentication failed for user..."?
I have an R shiny server which also hosts a PostgreSQL Database. However, I have trouble connecting R with Postgres.
Here is my R script:
library("dplyr")
library("RPostgreSQL")
con <- dbConnect(PostgreSQL(), dbname = "___", host="localhost",…
nexonvantec
- 572
- 1
- 5
- 18
1
vote
0 answers
Connect to Postgres via SSL using R on Windows
I'm trying to connect to a PostgreSQL database from R using SSL with the RPostgreSQL package (On windows).
dbConnect(dbDriver("PostgreSQL"),
user = user,
password = password,
host = host,
dbname =…
Julien Navarre
- 7,653
- 3
- 42
- 69
1
vote
2 answers
Timezone and POSIXct handling in RpostgreSQL
I'm having an issue with datetime handling in RPostgreSQL. Specifically it relates to POSIXct objects with a UTC timezone being automatically adjusted to daylight saving during upload to a postgres database. A simple…
Sam Weber
- 11
- 3
1
vote
0 answers
Unexpected timezones using psql in R
I am located in the Eastern Standard Timezone
> Sys.time()
[1] "2017-12-04 11:08:12 EST"
Using RPostgreSQL, I am trying to create a series of 10-minute intervals on a given date.
intervals <- dbGetQuery(con, " select i from…
iskandarblue
- 7,208
- 15
- 60
- 130
1
vote
0 answers
Collect postgres numeric NaN values as NaN/NA in R?
I am having an issue getting NaN numeric data with dplyr (0.5.0) from a PostgreSQL column of type numeric (and thus supports NaN). The underlying fault is in RPostgreSQL, which reads NaN values as zero. E.g.:
# create table
create table…
Alex
- 15,186
- 15
- 73
- 127
1
vote
0 answers
dply::left_join with postgres backend not working?
I'm running something like this: (sorry it is not 100% reproducible, unless you have postgres running in your machine, with mydb and tables created, and the given user and password)
library(RPostgreSQL)
library(tidyverse)
library(dbplyr)
pg_conn <-…
elikesprogramming
- 2,506
- 2
- 19
- 37
1
vote
1 answer
OVER() function in R using sqldf without RPostgreSQL?
I'm dealing with some sensitive data, so I'm concerned about using RPostgreSQL. I have all the data necessary loaded into dataframes in R. I'm trying to run queries on the data using the sqldf() function in R. These queries were written for Oracle…
MakoEyedSoldier
- 35
- 3
1
vote
1 answer
How to update rows in Database with values from data.frame in R conditionally
I've a data.frame in R - matches with some 1000+ rows
names(matches)
[1] "name" "c_id" "fname" "lname" "address" "zip_code" "Weight"
nrow(matches)
[1] 1253
I have a postgresql database table - list_m with following columns
db_name, db_cid,…
Avinash Sonee
- 1,701
- 2
- 15
- 17
1
vote
1 answer
R - RPostgreSQL Package - dbWriteTable to non-default schema where target table contains more fields than dataframe
The Issue
I am attempting to copy the contents of an R dataframe df to a PostgreSQL table table_name located in schema schema_name. By default, PostgreSQL will write tables to the public schema and I do not want to change this setting. The two…
aakash
- 143
- 6