How to I change the order of my columns so it doesn't break an import with pg

I'm trying to export data from a live Postgres DB into a local Postgres DB and have used pg_dump (via DataGrip) to dump the database into a tar file. I am using the INSERT option from the DataGrip dialogue which is giving the following.

--dbname="XXX" --format=t --file="XXX/DataGrip Backup/{timestamp}" --inserts

When I try and import the tar file using pg_restore (once again through DataGrip), one of my tables (the most important one!) won't import.

The error I'm getting is:

pg_restore: error: could not execute query: ERROR: invalid input syntax for type numeric: "AUD"

The table has a numeric field and a varchar field. One for the discount and one for the currency being used.

This is from a model (Django) using the MoneyField:

coupon_discount = MoneyField(
    decimal_places=2, max_digits=10, null=True, default_currency="AUD", default=0.00
)

An example of the data I am importing is:

Command was: INSERT INTO public.customers_customer VALUES (2557, '', NULL, 3, NULL, NULL, NULL, NULL, '2022-08-14 08:45:50.466306 00', '2022-08-14 08:45:50.466454 00', '2022-08-14 08:45:50.466459 00', 2559, '90h0nbJgDU', 0, 0.00, 'AUD', false, false, NULL, 1);
pg_restore: error: could not execute query: ERROR:  invalid input syntax for type numeric: "AUD"
LINE 1: ...10:31:35.347403 00', 2560, 'o4VxTmqlOa', 0, 0.00, 'AUD', fal...

When I look at the external Postgres DB the order of the two fields in the table is:

I assume this is the order the fields are given in the tar field. But when I check the local DB the order is different:

I don't think I've done anything to have it this way as Django is creating the tables and hence I assume the order?

Is there a way I can change this to have the data imported?

Do I need to export the data in a different way? I really just want the actual data to be imported. I already seem to have to use the --ignore-triggers somewhere to stop a whole host of problems with migration tables and others.

CodePudding user response：

Use the option --column-inserts (instead of --inserts) for pg_dump.

That will generate INSERT statements that will qualify the target columns.