1

The Issue

I am attempting to copy the contents of an R dataframe df to a PostgreSQL table table_name located in schema schema_name. By default, PostgreSQL will write tables to the public schema and I do not want to change this setting. The two unique aspects of this transfer are:

  1. Writing to a table under a non-default schema; and
  2. The dataframe df contains a fewer number of fields than table_name. All the fields contained in df, however, do exist in table_name.

What I've Tried

I first attempted to use dbWriteTable from the RPostgreSQL package by using a workaround:

dbWriteTable(con, c("schema_name","table_name"), df, append = T)

resulting in the following exception:

Error in postgresqlgetResult(new.con) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  missing data for column "extra_col"
CONTEXT:  COPY df, line 1: " [removed contents] "

I then attempted to us dbWriteTable2 from the caroline package (a wrapper for the aforementioned dbWriteTable function), but the non-default schema hack employed above does not appear to work:

dbWriteTable2(con, c("schema_name","table_name"), df, append = T, add.id = FALSE)

creates the following exception:

creating NAs/NULLs for for fields of table that are missing in your df 
Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  relation "schema_name" does not exist 
LINE 1: SELECT * FROM schema_name ORDER BY id DESC LIMIT 1
aakash
  • 143
  • 6
  • What about SQL `insert` statements, looping over rows ? – Dirk Eddelbuettel Nov 08 '16 at 03:10
  • That's likely my fallback option if I can't get the above to work, but I'd rather avoid that since this is being done for multiple dataframes/tables, some of which are fairly large. – aakash Nov 08 '16 at 04:03
  • 2
    And why not adding the missing null fields before the query ? df$extr_col1 <- NA ; df$extr_col2 <- NA ;... then dbWriteTable() ? – mdag02 Nov 08 '16 at 10:57
  • Used a variation of this in the end. Thanks! Would still, however, be interested in a direct solution to the issue. – aakash Nov 12 '16 at 02:59
  • I add that as an answer then... – mdag02 Nov 14 '16 at 11:31

1 Answers1

1

Add the missing null fields before the query :

df$extr_col1 <- NA
df$extr_col2 <- NA
...

then run your original dbWriteTable()...

mdag02
  • 1,035
  • 9
  • 16