search

r-dbi / DBI

A database interface (DBI) definition for communication between R and RDBMSs
https://dbi.r-dbi.org
GNU Lesser General Public License v2.1
298 stars 74 forks source link

FR: `dbWriteTable()` should compare input and output column names. #399

Open mgirlich opened 2 years ago

mgirlich commented 2 years ago

I just learned that (some) databases seem to (silently?) change invalid column names (see https://github.com/tidyverse/dbplyr/issues/1016). This causes issues with the new optimisation in dbplyr::copy_to() where column names do not match. To handle this correctly it would be necessary for copy_to() to revert to the old behaviour and query the column names again. To avoid this extra roundtrip with the database it would be great if dbWriteTable() would warn (or even error) if the output column names are different from the input column names. @krlmlr What do you think?

krlmlr commented 2 years ago

Thanks. How would you query the output column names? How much work would we need to do in the respective backends or in DBI?

mgirlich commented 2 years ago

There is no possibility to directly get back the column names when creating the table, is there? dbplyr queries them via SELECT * FROM <tbl> WHERE 0 = 1 but of course it would be nice if we could avoid this extra query to the database. Maybe don't query the names if they are very simple (ASCII + lowercase)?

krlmlr commented 1 year ago

I'm not aware of a way to achieve this in one query.

krlmlr commented 7 months ago

What's the current copy_to() behavior?