I have the same table in two postgresql databases. I want to copy from one to the other, but due to migrations the order of the columns is different,so I need tell Copy a description of the source data, which Copy allows.
If I export the file to a csv with headers, and then use those headers to describe the data, I get an error which is puzzling me. I am using csv mostly for the convenience of the header option, which helps me explain my question better. I understand that the header row has no role in the copy from process.
To make the CSV:
psql "host=host1 user=django_api_sync dbname=django_api_sync port=5433 password=B" -c "COPY cached_dear_dearcache TO stdout (FORMAT csv, HEADER 1) " > data.csv
the headers row names the columsn exactly as I expect and apart from the order, the names are the same as in the destination datbase. The first column is object_uniqueID
so I try this:
psql "host=localhost port=5433 user=django_api_sync dbname=django_api_sync password=d" -c "COPY cached_dear_dearcache (object_uniqueID,object_type,last_modified,jdata,value,dear_account_id,flag1,string_uniqueID,aux_field1,aux_field2) from 'data.csv' (FORMAT csv, HEADER 1)"
but I get this error:
ERROR: column "object_uniqueid" of relation "cached_dear_dearcache" does not exist
The case of the column name is different. The column definitely exists.
This is the header row from the csv dump from the source:
object_uniqueID,object_type,last_modified,jdata,value,dear_account_id,flag1,string_uniqueID,aux_field1,aux_field2
This is the header row when I dump data from the destination:
object_uniqueID,object_type,last_modified,dear_account_id,jdata,value,flag1,string_uniqueID,aux_field1,aux_field2
So the error makes no sense to me.
psql --version psql (PostgreSQL) 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1)
the destination db is v10. The source db is v12.5
