Syntax for R dbConnect

Hello,

I searched thoroughly for an answer to my question before composing this post.

Would someone be so kind as to tell me the proper syntax for setting up a connection to my CockroachDB cluster using dbConnect within R?

Here’s what I have:

con = dbConnect(dbDriver(“Postgres”),
dbname = “my-db”,
host = “free-tier4.aws-us-west-2.cockroachlabs.cloud”,
port = ‘26257’,
user = “myUserName”,
password = ‘myPassword’,
sslmode = “verify-full”,
sslrootcert= paste0(Sys.getenv(‘APPDATA’), “\postgresql\root.crt”),
options = “–cluster=my-database-1441”, # ← This is probably wrong.
)

When I run the code above I get an error saying, “Error: Failed to fetch row: ERROR: SET datestyle takes only one argument.”

I was able to connect to my CockroachDB cluster using DBeaver.

Please advise.

Don

Welcome to the forum @DSTMalo!

We haven’t tested CCockroachDB with R before, so you are in somewhat new territory. The somewhat good news is that the error “SET datestyle takes only one argument.” comes from within CockroachDB, so that means your connection actually was created successfully, but it hit an error immediately.

I looked at the RPostgres source code, and it looks like it comes from this line: RPostgres/PqConnection.R at e9262d59aafd5101b75cc3802b8374404a84ca13 · r-dbi/RPostgres · GitHub

It runs the query SET datestyle to iso, mdy;. That won’t work (and actually in my testing it didn’t work against Postgres either). The correct way would be SET datestyle to 'iso, mdy'; with the single-quotes added.

I’d recommend filing an issue/PR with the maintainers of RPostgres so that the query can be updated.

Thank you for your kind response!

I have opened an issue on RPostgres’ GitHub issues page, and I included the information about how to update the SET datestyle query.

When and if they fix the problem I’ll post an update to this thread.

This issue was fixed in RPostgres version 1.4.2.

I was able to successfully upload a table from R to Cockroach DB.

Thanks for following up with them!