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
3
votes
0 answers
dbWriteTable with different column names between data.frame and database
Version 0.4 of the dbWriteTable package allowed to copy a data.frame to a database table even if the column names of the two objects did not match. Current version (0.6) instead throws an error in such a situation.
Is there a way to bypass the…
msaltieri
- 162
- 1
- 9
3
votes
2 answers
How to use dynamic values while executing SQL scripts in R
My R workflow now involves dealing with a lot of queries (RPostgreSQL library). I really want to make code easy to maintain and manage in the future.
I started loading large queries from separate .SQL files (this helped) and it worked great.
Then I…
Pranasas
- 597
- 6
- 22
3
votes
2 answers
How to properly handle timezone when passing POSIXct objects between R and Postgres DBMS?
I am struggling to understand what exactly happens behind the scenes when passing POSIXct objects between R and Postgres using RPostgreSQL. In the following example, I define two timestamp fields: one with a timezone the other one without. However,…
Mark Heckmann
- 10,943
- 4
- 56
- 88
3
votes
2 answers
Select rows from Postgres where column matches any string in an array
This is basic, and I apologize if I missed the existing answer. I am trying to select rows in a db where the userid matches any string in an array I provide. Here are the methods I've tried:
query_string = "SELECT * FROM data WHERE userid =…
helloB
- 3,472
- 10
- 40
- 87
3
votes
2 answers
R Shiny breaks PostgreSQL authentication with .pgpass
I have my password to database stored in pgpass.conf file. I am connecting to database from R with RPostgres, without specifying password so it is read from pgpass.conf, like this:
con <- dbConnect(RPostgres::Postgres(),
dbname =…
BartekCh
- 920
- 6
- 15
3
votes
0 answers
?tcpKeepAlive=true and RPostgres
The suggest url for my redshift server has a ?tcpKeepAlive=true appended to it, so that it looks something like
jdbc:postgresql://myserver:myport/dbname?tcpKeepAlive=true
Now, the RPostgres::dbConnect function has signature
dbConnect(dbname = NULL,…
StevieP
- 1,569
- 12
- 23
3
votes
1 answer
Error on dbSendQuery with RPostgreSQL and RJDBC on CentOS 6.5 and not Windows 7
I've run into a strange problem that occurs on my CentOS 6.5 box hosted on AWS that does not occur on my Windows machine.
When sending a large dbSendQuery (string with 15,000+ bytes/characters), I get error messages. With smaller queries, it runs…
Ken Yeoh
- 876
- 6
- 11
3
votes
1 answer
Same query, different results. Possible causes?
For testing purposes, I am querying the same table from the same database using two different GUIs (RStudio and SquirreLSQL).
The query in the SquirreLSQL console looks like this:
select count(distinct idstr) from fact_table where…
zebrainatree
- 351
- 1
- 3
- 15
3
votes
3 answers
Check that connection is valid
I'm using RPostgreSQL and sqldf inside my function like this:
MyFunction <- function(Connection) {
options(sqldf.RPostgreSQL.user = Connection[1],
sqldf.RPostgreSQL.password = Connection[2],
…
Tomas Greif
- 21,685
- 23
- 106
- 155
3
votes
2 answers
How do I interrupt a RPostgresql query in R
What is the best way to interrupt a long-running query in RPostgresql?
For example, I wanted to see the first 10 rows of a table and meant to type,
dbGetQuery(con,"
select * from big.table
limit 10
")
But I sometimes leave out the "limit…
d_a_c321
- 533
- 1
- 11
- 23
3
votes
2 answers
Exporting Table from R to PostgreSQL by RPostgreSQL
I am using RPostgreSQL I have done number of process and I got a table in R. I wanted to put this table in PostgreSQL from R for further analysis that I will use PostgreSQL codes. The problem is that when I have a table in R which doesn't exist in…
A.Amidi
- 2,502
- 5
- 25
- 37
2
votes
2 answers
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 run my query.
For example, I have a script that…
spencergadd
- 57
- 6
2
votes
0 answers
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"
here is the code that produces the error
sourcing it…
user17783243
- 21
- 2
2
votes
0 answers
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 library.
pg <- dbDriver(drvName = "PostgreSQL")
…
Coon
- 87
- 7
2
votes
2 answers
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 RPostgreSQL.
dbWriteTable(con, 'mtcars',…
codedancer
- 1,504
- 9
- 20