Redshift varchar character length not respected by dplyr::rows_*()

[samterfa]

Redshift varchar character length not respected by dplyr::rows_*()

[krlmlr]

Thanks. Can you use dbAppendTable() or the like to create a temporary table on the database, and then use rows_append(copy = FALSE) to copy to the persistent table?

In the dbplyr issue, I see that dbWriteTable() has the same problem as rows_append() . Can you please provide more detail in how dbWriteTable() compares to dbAppendTable() ? What happens if the table is created with dbCreateTable() and then data is appended with dbAppendTable() ?

What does dbDataType() return for your data?

[samterfa]

dbAppendTable() actually works fine already for appending data to a Redshift table with the appropriate datatype. This is why I thought the issue might be a dplyr or dbplyr issue instead. dplyr::rows_append() is broken for me and should work, but it's not my main concern at the moment since I can use the dbAppendTable() workaround.

What I'm really after is dplyr::rows_update() which is a really simple way to update rows of a database. I haven't found a good workaround for this. I am having trouble tracing what dplyr::rows_update() is doing and how the ecosystem of RPostgres, dbplyr, and dplyr work together.

[krlmlr]

Thanks. I'm typically using separate steps for bringing data into the database and updating a live data model:

1. Copy data as temporary table
2. Use rows_xxx(copy = FALSE) (the default)

This is a bit more tedious, but gives the best control.

[krlmlr]

Realizing I didn't really answer your rows_update() question.

rows_update() and the other rows_*() functions work best if the data is already in the database, e.g., in a temporary table. Then you can use update = FALSE to avoid copying, this simplifies the procedure. You prepare a table that contains the keys and new values you want to update, copy it to the database, and call rows_update() . Does that help?

[samterfa]

We have moved to BigQuery so I am closing the issue. Thanks for all your help!