I am trying to copy the data from one database to another database. Both the source and target tables have 2 geometry columns -- a polygon field and a point field. There are
NOT NULL constraints on both of these columns.
My ogr2ogr command looks like this
ogr2ogr -f "PostgreSQL" -append -update -sql "select polygon_geom, centre_geom, id, category from sourceschema.table1" PG:"dbname=db1 host=host1 port=5432 user=user password=password" PG:"dbname=db2 host=host2 port=5432 user=user password=password" -nln targetschema.table1
However I can't get ogr to recognise the 2 geometry fields. The error i get is:
ERROR 1: ERROR: null value in column "geom" violates not-null constraint
How can I copy this data across my 2 databases properly? Even if I export just one geometry column, the NOT NULL constraint on the other column will still prevent me from inserting data. Or do I just lift the not null constraints for the time I'm transferring data across?
EDIT: The source and target tables are not exact copies of one another - the target table has more columns than the source.