Creation of empty tables on PG DB works but throws error

[TSchiefer]

IIRC, roughly the workflow is the following when calling copy_dm_to():

1. create empty tables on DB
2. set keys
3. insert data

I would like to copy only empty tables, to prepare a DB for later insertion of data.
The result after the call is the desired one: the tables are there and key relations are all set, but the function runs into a dbAppendTable()-call which fails.

I think even if there is another preferrable method for doing this, we could make this work.

[krlmlr]

So, dbAppendTable() fails on Postgres for empty tables? We need to fix there, or work around here.

Does it work with SQL Server?

[TSchiefer]

It works with SQL Server

[krlmlr]

Thanks! Worked around here, can you please open an issue in DBItest so that we can add a test there and fix all affected backends?

[TSchiefer]

I need to apologize, the issue description was a bit misleading:
When creating a reprex I realized that the culprit was the argument copy = FALSE:

library(tibble)
con_pg <- DBI::dbConnect(RPostgres::Postgres())
t1 <- tibble(a = integer())
DBI::dbWriteTable(con_pg, "t1", t1, temporary = FALSE)
DBI::dbAppendTable(con_pg, "t1", t1)
#> [1] 0
# This is the error that we're seeing in `copy_dm_to()`:
DBI::dbAppendTable(con_pg, "t1", t1, copy = FALSE)
#> Error: Failed to prepare query: ERROR:  syntax error at or near ")"
#> LINE 4:   ()
#>            ^
DBI::dbAppendTable(con_pg, "t1", t1, copy = TRUE)
#> [1] 0
DBI::dbExecute(con_pg, "DROP TABLE t1")
#> [1] 0

^{Created on 2022-02-28 by the reprex package (v2.0.1)}

[TSchiefer]

Your workaround does of course solve it nevertheless