How to correctly append data from ArcInfo GENERATE file to PostGIS table using ogr2ogr

Question

Using CentOS 8.0 with PostgreSQL 12, PostGIS 3, and GDAL/OGR 3.0.2. I am trying to append data in an ArcInfo GENERATE file to an exisiting table.

My ArcInfo GENERATE file input.gen looks like this:

1 11.250000 58.282526
2 -168.750000 58.282526
3 -78.750000 69.094843
4 -123.750000 35.264390
5 -78.750000 31.717474
6 -33.750000 35.264390
7 -57.844843 -0.000000
8 -20.467474 -0.000000
9 11.250000 20.905157
10 11.250000 -20.905157
11 42.967474 -0.000000
12 56.250000 35.264390
13 80.344843 -0.000000
14 101.250000 31.717474
15 101.250000 69.094843
16 146.250000 35.264390
17 -137.032526 -0.000000
18 -99.655157 0.000000
19 -123.750000 -35.264390
20 -78.750000 -31.717474
21 -33.750000 -35.264390
22 -78.750000 -69.094843
23 11.250000 -58.282526
24 56.250000 -35.264390
25 101.250000 -69.094843
26 101.250000 -31.717474
27 122.155157 0.000000
28 146.250000 -35.264390
29 159.532526 -0.000000
30 -168.750000 20.905157
31 -168.750000 -20.905157
32 -168.750000 -58.282526
END

The PostGIS table is created with:

CREATE TABLE myschema.points
(
 gid bigint NOT NULL
 CONSTRAINT points_pkey
 PRIMARY KEY,
 geog geography not null
);
CREATE INDEX points_gist
 ON myschema.points (geog);

Constraints:

• The table column names cannot be changed.
• The ID in the ArcInfo GENERATE file must be retained and loaded into the gid
• If the gid already exists, ignore the error and continue. Similar to PG's ON CONFLICT DO NOTHING

The command I tried:

ogr2ogr -doo "ACTIVE_SCHEMA=myschema" \
 -append \
 --config PGSQL_OGR_FID gid \
 -nln points \
 -sql "SELECT ID AS gid FROM input" \
 -f "PostgreSQL" PG:"dbname= user= password= host= port=5432" \
 input.gen

But this results in a not-null constraint violation

ERROR 1: ERROR: null value in column "gid" violates not-null constraint

DETAIL: Failing row contains (null, 0101000020E610000000000000008026408522DDCF29244D40).

ERROR 1: INSERT command for new feature failed.

ERROR: null value in column "gid" violates not-null constraint

DETAIL: Failing row contains (null, 0101000020E610000000000000008026408522DDCF29244D40).

Command: INSERT INTO "points" ("geog" ) VALUES ('0101000020E610000000000000008026408522DDCF29244D40'::GEOGRAPHY)

ERROR 1: Unable to write feature 0 from layer centers.

ERROR 1: Terminating translation prematurely after failed translation from sql statement.

Note:

It seems the ID is not properly selected, so I tried id, fid, ogc_fid, and oid in the -sql statement instead, but no luck. Then I tried -preserve_fid, because it sounds like this option will preserve the fid of the input data. That command actually goes through without an error, but it does not load the data as in the input, instead it starts at 0.

How do I correctly select and insert the ID in the inputfile?

The ogr2ogr docs and ArcInfo GENERATE have helped me.

This may sound like my previous question, but it is not. It is a different issue.

• Vince: "ArcGEN" isn't the name of that format. "ArcInfo GENERATE" is closer, but for points it's a lot closer to plain old CSV (whitespace seperator). There is no uniqueness constraint on the USER-ID (first) column for GENERATE format, but that doesn't mean you should toss it aside. You don't seem to indicate if the data is loaded as X,Y or lat,lon (GENERATE is X,Y, so lon,let.
• Michael: @Vince, thanks, I changed the name. ogr2ogr seems to get the geometry right as the points are in the correct location when using -preserve_fid, but the ID is incorrect.
• Vince: gid and fid are generally serial columns; what happens if you add a serial ID and then map the first column to userid?
• Campa: @Michael could you successfully append your data into PostGIS?