Write directly to the data schema?

[anders-kolstad]

The upsert functions were first made to upload files to the temp schema and then subsequently move them to the data schema. This is good practice because if something happens during the upsert, say the network connection failes, then you don't end up with half a datatable uploaded to the data schema meaning you would have to manualy go and remove it before upserting again. Moving from temp to data is much quicker than upserting to temp from R, so there's less chance of things going wrong.

However, this operation requires that the data types of the temp table matches the table in the data schema table before moving it. If not, you get errors like this: ERROR: column "locationID" is of type uuid but expression is of type text. This conversion (CAST functino in SQL) is possible but I've not found a very smooth and safe way of doing this. But if you upsert directly to the data schema, this conversion is automatic.

I don't think writing to temp is that necessary.

PS: The upsert_locations script shows an example of writing directly to 'data'.

[andersfi]

probably a good idea, the dbWriteTable table function has control settings that can be used for fine-tuning

[vmzomdav]

to cast a uuid text as uuid format use the following code "::uuid" after the uuid value enclosed in a single quote. example '0008d991-aecc-4bc8-91e1-3690c1e6a0b3'::uuid.