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

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?

Oct 19 '22 05:10 mgirlich

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

Nov 02 '22 05:11 krlmlr

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)?

Nov 15 '22 08:11 mgirlich

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

Nov 19 '22 14:11 krlmlr

What's the current copy_to() behavior?

Apr 01 '24 17:04 krlmlr