Polygon/multipolygon columns: incorrect write, correct read

[pachadotdev]

Polygon/multipolygon columns: incorrect write, correct read

[krlmlr]

Closing, fixed downstream. Thanks @dpprdan!

[dpprdan]

Fixed in {sf}: r-spatial/sf#2223

[dpprdan]

This only concerns the sf::dbWriteTable() method, so I think this is better handled there than here.

However, this is partly caused by RPostgres::dbWriteTable() stop()ing if append && !is.null(field.types)

RPostgres/R/dbWriteTable_PqConnection_character_data.frame.R

Lines 24 to 26 in 58a052b

	if (append && !is.null(field.types)) {
	stopc("Cannot specify `field.types` with `append = TRUE`")
	}

This also happens when the table does not (yet) exist, i.e. when there is nothing to append to.

Do you think it would be possible to move this check a few lines down and only stop if the table is found, @krlmlr?

RPostgres/R/dbWriteTable_PqConnection_character_data.frame.R

Line 47 in 58a052b

found <- dbExistsTable(conn, name)

Right now, dbWriteTable will create the table anyway if no field.types are provided.

RPostgres/R/dbWriteTable_PqConnection_character_data.frame.R

Lines 60 to 63 in 58a052b

	if (!found || overwrite) {
	if (is.null(field.types)) {
	combined_field_types <- lapply(value, dbDataType, dbObj = conn)
	} else {

The downside is that dbWriteTable(append = TRUE, field.types = "something") will work when the table does not exist but stop when it does. This might be confusing. However, we could ameliorate this by issuing a message "You wanted to append, but the table does not exist yet so we created it".

Of course, third-party methods have to provide custom field.types for their datatypes, like sfc, so field.types cannot be NULL for them. So they cannot create a table currently if append = TRUE but the table does not exist.

[krlmlr]

Thanks, missed that. IIUC, we can fix this by fixing sf::dbWriteTable()?

[dpprdan]

Thanks, missed that. IIUC, we can fix this by fixing sf::dbWriteTable()?

Yes. But the fix is a bit ugly (IMO, i.e. overriding function arguments).

IMHO it would be best, i.e. cleanest, if append were only possible if the table already exists, but that's probably too much of a breaking change.

[krlmlr]

There are new generics dbCreateTable() and dbAppendTable(), does sf support these?

[dpprdan]

Ha, I just had a similar thought, i.e. refactor sf::dbWriteTable() using dbCreateTable() and dbAppendTable().

So no, sf does not support/use them yet.

Anyway, I am not convinced by my "create table if append = TRUE" proposal above myself anymore, so this can be closed IMO.