2

I'm using citus columnar extension cstore_fdw for PostgreSQL (I'm on 9.4.1).

I create the foreign table without any problem.

The problem starts in R when I try to write into it.

A normal dbWriteTable command does not work:

cba <- dbWriteTable(conpg, name=dataDt1_, value=df, row.names=FALSE, overwrite=TRUE)
Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"PostgreSQLConnection", "db.table", "data.frame"’

Considering that for PostgreSQL a foreign table can be a csv, a table on another server, a columnar store etc., does anybody have experience accessing any of the above with R?

metdos
  • 13,411
  • 17
  • 77
  • 120
Enzo
  • 2,543
  • 1
  • 25
  • 38
  • I've just found out that cstore does not yet support `INSERT` `ALTER` and `DELETE`: this could explain why I cannot write into the table. On the other hand my question stands to see if anybody has experience in accessing any (other) type of foreign table with R. – Enzo Mar 13 '15 at 00:26

1 Answers1

2

cstore_fdw doesn't support UPDATE and DELETE. version 1.2 added support for INSERT INTO cstore_table SELECT ..., but the support for single row inserts is still missing.

Currently you can append data to a cstore table in one of the following ways:

  • Use the COPY command
  • Use INSERT INTO cstore_table SELECT ...
Samay Sharma
  • 7
  • 2
Hadi Moshayedi
  • 846
  • 7
  • 12
  • thanks this clarifies the situation with cstore_fdw (great product bdw). I am still a bit at a loss on how to use it with R. I will try ```dbSendQuery``` from the ```DBI``` package and report. – Enzo Mar 23 '15 at 19:38