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
R for loop is only storing one result?
I am trying to get the row counts of all my tables with a query and I want to save the results in a dataframe. Right now, it only saves one value and I'm not sure what the issue is. Thanks for any help.
schema <- "test"
table_prefix <-…
mjoy
- 606
- 2
- 9
- 19
0
votes
2 answers
RPostgreSQL - Passing Parameter in R to a Query in RPostgreSQL
Question: How do I pass a variable in the RPostgreSQL query?
Example: In the example below I try to pass the date '2018-01-03' to the query
library(RPostgreSQL)
dt <- '2018-01-03'
connect <- dbConnect(PostgreSQL(),
…
Daniel
- 83
- 1
- 1
- 10
0
votes
1 answer
Querying mixed case columns in SQL with R
I have a mixed case column in my_table that can only be queried using double quotes in psql. For example:
select "mixedCase" from my_table limit 5; would be the correct way to write the query in psql, and this returns records successfully
However,…
iskandarblue
- 7,208
- 15
- 60
- 130
0
votes
1 answer
Pasting another variables results into my SQL query in R
So I'm using these three packages in R in order to connect to some databases and query them:
library(rJava)
library(RJDBC)
library (RPostgreSQL)
id_query <- dbGetQuery(conn2, "SELECT b.id id FROM table1 a LEFT JOIN table2 b ON a.id = b.id WHERE…
user9302275
0
votes
1 answer
renderDataTable not working when data pulled from Redshift
I have a shinydashboard (using the shinydashboard package here) where I take input from the user for his username and password, connect to Redshift using it,execute a Redshift query using his credentials and then want to display the output of the…
Gompu
- 415
- 1
- 6
- 21
0
votes
1 answer
Prepared statement in R - dbSendQuery does not work with dates
I am trying to write a prepared statement with dbSendQuery. My issue is that the data frame of inputs are converted to numeric values, but two of the three inputs are dates. This results in the following error message:
Warning: Error in…
Toby Tan
- 1
- 1
0
votes
1 answer
Copy from file ERROR: invalid input syntax for integer: "1"
I'm using PostgresSQL 10 and RPostgreSQL package in R.
I create a new table:
CREATE TABLE people (
id integer NOT NULL,
name character varying,
birthdate date,
deathdate date
);
Then attempt to copy the file :
COPY people (id,…
user27612
- 55
- 1
- 7
0
votes
2 answers
selecting set of second lowest values
I have two columns of interest ID and Deadline:
ID Deadline (DD/MM/YYYY)
1 01/01/2017
1 05/01/2017
1 04/01/2017
2 02/01/2017
2 03/01/2017
2 06/02/2017
2 08/03/2017
Each ID can have multiple (n)…
michal
- 3
- 2
0
votes
1 answer
Wrong text encoding returned when query from PostgreSQL database using RPostgreSQL
I used R and DBI to query data from a PostgreSQL but got corrupted results with weird characters. I check both server encoding and client encoding, and they are all UTF-8. Here's what I did
Connect to the db
library(DBI)
conn <- dbConnect(
drv…
Tue Nguyen
- 73
- 1
- 6
0
votes
0 answers
RPostgreSQL - Nested selects
I am trying to:
dbGetQuery(conn, "SELECT * FROM test WHERE id in (select fid from test2)")
However, I get the error:
RS-DBI driver: (could not Retrieve the result : ERROR: column "fid" does not exist
Is nested selects not supported by…
Tino K Stigsen
- 1
- 1
0
votes
1 answer
Can't connect to postgresql in R if dbname contains € character
I need to access different databases which contain symbols $ and € (or other currencies)
library(RPostgreSQL)
get_connection <- function(connection = current_connection)
{
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = connection,
…
Дмитрий Регент
- 53
- 7
0
votes
0 answers
Able to connect AWS Redshift via dbConnect but not src_postgres
I am to connect to AWS Redshift using dbConnect query
conn_loss <- dbConnect(drv, host="hydrogen2.YOURHOST.us-east-1.redshift.amazonaws.com",
port="5439",
dbname="mydb",
user="master_user",
…
Jaimik Jain
- 79
- 1
- 4
0
votes
1 answer
megaptera::dbPars() won't connect with PostgreSQL
I want to use the megaptera::dbPars() function as per the megaptera vignette, however I keep receiving errors when executing the function. My PostgreSQL can be accessed through pgAdmin 4 as well as through the command line and works fine. The…
Greg Nelson
- 33
- 6
0
votes
1 answer
RPostgreSQL - import large table into R without losing data
I'm trying to import a very large data that includes timestamp formatted as "2015-08-31 07:23:25.3"
The table has almost 2.5mil rows and when I import it into the dataframe, it loses time.
table1 <- dbReadTable(db_connection, "table_name")
Above is…
user5813583
- 133
- 10
0
votes
2 answers
How to arrange RPostgreSQL query and create covariance matrix in R
I want to create a covariance matrix from a data frame which is not yet suitable for creating one.
After using RPostgreSQL to query the database I have a data frame of the following type:
pg_id item_id value date
1 …
Elia
- 151
- 1
- 1
- 8