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
2
votes
1 answer
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 code
db_con <- dbConnect(
…
user17911
- 1,073
- 1
- 8
- 18
2
votes
2 answers
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 one year earlier than the initial dates.
To be…
NoCaresBear
- 53
- 6
2
votes
2 answers
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 <- dbConnect(RPostgres::Postgres()
,…
psysky
- 3,037
- 5
- 28
- 64
2
votes
2 answers
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 the csv files and tried to import.
R then returns an…
Gonny
- 37
- 5
2
votes
0 answers
Speeding up parallel SQL querying for R?
I have a dataframe df with an id column. This maps to many rows 1:n) in my database table. Querying each ID sequentially takes about an hour to complete, so I'm trying to run multiple queries at once using the doparallel package. There is overhead…
CorerMaximus
- 653
- 5
- 15
2
votes
0 answers
Difference between DBI package and RPostgreSQL packages in R
I'm building a R package that connects to a PostgreSQL database. I'd like to explicitly state package::function for my functions that depend on external packages. Here is a function from that package:
connect_aact <- function(user, password) {
…
user8248672
2
votes
1 answer
how to pass value stored in r variable to a column in where clause of postgresql query in R
I am using RPostgresql and DBI in RStudio.
library(RPostgreSQL)
library(DBI)
#save password
prod_pw <- {
"my_pass"
}
# make db connection
con <- dbConnect(RPostgreSQL::PostgreSQL(), dbname = 'my_dbname',
host =…
kRazzy R
- 1,561
- 1
- 16
- 44
2
votes
2 answers
How to get the column names from Database in R?
How to get the only particular table column names using R?
Sample code:
df<-dbgetQuery(con,"select * from table 1 limit 100")
colnames(df)
Is there any alternative for the above query?
sai saran
- 737
- 9
- 32
2
votes
0 answers
change PostgreSQL schema inside a loop using RpostgreSQL
I have a bunch of identical schemas in PostgreSQL I want to get data from. I use RpostgreSQL inside a loop this way
``` r
results <- data.frame()
for (schema in schema.list ) {
con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
…
Forge
- 1,587
- 1
- 15
- 36
2
votes
2 answers
How to secure database username and password with R DBI?
When connecting to a database using the dbConnect function in the DBI package, what are the best practices for securing logon information such as database name, username and password?
Entering logon information as a character text such…
ichbinallen
- 1,019
- 12
- 18
2
votes
0 answers
No applicable method for 'st_write' applied to an object of class "c('tbl_df', 'tbl', 'data.frame')"
I am trying to transfer data from the Thingspeak API into a postgres database. The API limits each request to 8000 observations, but I need to pull millions! I'm using R to iteratively pull from the API, do a bunch of wrangling, and then submit the…
philiporlando
- 941
- 4
- 19
- 31
2
votes
1 answer
R crashes when establishing a database connection via RPostgreSQL
I am using R version noted below:
R version 3.4.3 (2017-11-30) -- "Kite-Eating Tree"
Copyright (C) 2017 The R Foundation for Statistical Computing
Platform: x86_64-apple-darwin15.6.0 (64-bit)
R is free software and comes with ABSOLUTELY NO…
Brash Equilibrium
- 1,357
- 4
- 14
- 35
2
votes
2 answers
R package was installed by version with different internals
On my Win 7 Ent x64 I installed RStudio R-3.4.2.
I installed few packages that I need (RPostgreSQL, sqldf, etc..)
But when I'm executing code, these libraries give me errors:
library(RPostgreSQL)
Error: package ‘RPostgreSQL’ was installed by an R…
Lukáš Egrt
- 31
- 1
- 2
2
votes
1 answer
Shiny-server can't connect to PostgreSQL
I have a shiny dashboard on a Ubuntu server that connects to the 'local' PostgreSQL db to collect data. It works perfectly well when I launch the dashboard from my R-Studio environment (on the same server), but the dashboard fails to load when I…
Andre Marais
- 31
- 3
2
votes
1 answer
Source only a single variable from a script
I have a postgresql database connection and want to get a table from the database. Presumably it's good practice to keep the connection info in a different file?
I have two files just now:
#getthetable.R
library(tidyverse)
library(dbplyr)
##…
Doug Fir
- 19,971
- 47
- 169
- 299