Inserting value to column which is not existing in input using ogr2ogr

Question

I am inserting geopackage to a table in the PostgreSQL if a certain value is not null with the following comment

ogr2ogr -append -nln pgtablename  -f PostgreSQL "PG:host=xx user=xx dbname=xx password=xx"  E:\workspace\DATA_WORK\data\edit\70319.gpkg Bauwerkslinie -sql "SELECT * from Bauwerkslinie where begehung_von IS NOT NULL"

It works as expected. In the table pgtablename, there is a column that I do not have in gpkg transactionid and I would like to insert a value, that will be always the same for the affected objects in the command above.

Any idea how can I do it?

Answer 1

If I understand correctly, you need to change your SQL from:

"SELECT * from Bauwerkslinie where begehung_von IS NOT NULL"

to

"SELECT *, 99 as transactionid from Bauwerkslinie where begehung_von IS NOT NULL"

where "99" is whatever value you want in the new column.

This works for me with a simple geopackage example.

If transactionid already exists in your data then select this first, before the star. This does not update CRESS_ID:

$ ogr2ogr out.gpkg nine.gpkg  -sql "SELECT *, 1001 as CRESS_ID from nine;"
$ ogrinfo -al out.gpkg | grep CRESS
CRESS_ID: Integer (0.0)
  CRESS_ID (Integer) = 86
  CRESS_ID (Integer) = 85
  CRESS_ID (Integer) = 79
  CRESS_ID (Integer) = 99

But this does:

$ ogr2ogr out.gpkg nine.gpkg  -sql "SELECT 1001 as CRESS_ID,* from nine;"
$ ogrinfo -al out.gpkg | grep CRESS
CRESS_ID: Integer (0.0)
  CRESS_ID (Integer) = 1001
  CRESS_ID (Integer) = 1001
  CRESS_ID (Integer) = 1001

The order of columns might be changed but the order of columns in a relational table model is not part of the model - nor is the order of rows!