0

I'm synchronizing data from AmigoCloud's table with a PostgreSQL table.

I'm using GDAL's ogr2ogr module in osgeo environment. The command I'm running is as follows:

ogr2ogr -append -f PostgreSQL PG:"dbname=`my_db_name` user=`my_user` password=`my_password` host=localhost port=5432" "AmigoCloud:`project_id` datasets=`dataset_id`" AMIGOCLOUD_API_KEY `my_api_key` -nln `my_schema.my_table`

The issue is that my table from AmigoCloud contains 3620 rows but when they are copied to local postgres through this command, it copies it twice and I get duplicated rows (7240 in total). How to prevent this?

I tried doing -lco FID=amigo_id as amigo_id column contains unique values (varchar) but ogr forcefully converts them to integer and gives the error:

"ERROR 1: Wrong field type for amigo_id"

1 Answer 1

3

Switch -append is adding new features into the table. If you already had a table with 3620 rows then the result is expected. If the table was empty then the result is strange.

If you want to replace the contents of the table use the config option OGR_TRUNCATE https://gdal.org/drivers/vector/pg.html

OGR_TRUNCATE: If set to “YES”, the content of the table will be first erased with the SQL TRUNCATE command before inserting the first feature. This is an alternative to using the -overwrite flag of ogr2ogr, that avoids views based on the table to be destroyed. Typical use case: ogr2ogr -append PG:dbname=foo abc.shp --config OGR_TRUNCATE YES.

4
  • I don't have any existing data, it is running twice for some reason. I tried using --config OGR_TRUNCATE YES but it still creates duplicate records.
    – RMT
    Mar 7, 2023 at 17:25
  • What number of features do you get with ogrinfo from amigocloud?
    – user30184
    Mar 7, 2023 at 17:37
  • I get the Feature Count as 3620
    – RMT
    Mar 7, 2023 at 17:40
  • Add --debug on into your original command, maybe you get a hint about what happens.
    – user30184
    Mar 7, 2023 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.