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\edit70319円.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!

• Houska: Beat me to it, @Spacedman. Was just about to say the same thing. stackoverflow.com/questions/2504163/… is a link to using constants as an alias.
• Nil: The columns in my gpkg should be in the same order as in the DB?
• Nil: @Spacedman If so, I think I should write the all fields name instead *. Because the columns are unfortunately not same order and a couple of colums are existing only in gpkg
• user30184: Try it and report the result.
• Spacedman: Possibly the geopackage might add a fid column. Otherwise that looks like a bug. The fields should be identical.