How to write tables into Panoply using RPostgreSQL?

Question

I am trying to write a table into my data warehouse using the RPostgreSQL package

library(DBI)
library(RPostgreSQL)

pano = dbConnect(dbDriver("PostgreSQL"),
                 host = 'db.panoply.io',
                 port = '5439',
                 user = panoply_user,
                 password = panoply_pw,
                 dbname = mydb)

RPostgreSQL::dbWriteTable(pano, "mtcars", mtcars[1:5, ])

I am getting this error:

Error in postgresqlpqExec(new.con, sql4) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or near "STDIN"
LINE 1: ..."hp","drat","wt","qsec","vs","am","gear","carb" ) FROM STDIN
                                                                  ^
)

The above code writes into Panoply as a 0 row, 0 byte table. Columns seem to be properly entered into Panoply but nothing else appears.

score 1 · Accepted Answer · answered Feb 18 '20 at 07:41

Fiest and most important redshift <> postgresql.

Redshift does not use the Postgres bulk loader. (so stdin is NOT allowed).

There are many options available which you should choose depending on your need, especially consider the volume of data.

For high volume of data you should write to s3 first and then use redshift copy command. There are many options take a look at https://github.com/sicarul/redshiftTools

for low volume see inserting multiple records at once into Redshift with R

How to write tables into Panoply using RPostgreSQL?

1 Answers1