3

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?

6

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!

8
  • 1
    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. – Houska Mar 5 at 10:41
  • The columns in my gpkg should be in the same order as in the DB? – Nil Mar 5 at 10:42
  • @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 – Nil Mar 5 at 10:49
  • Try it and report the result. – user30184 Mar 5 at 10:54
  • 1
    Possibly the geopackage might add a fid column. Otherwise that looks like a bug. The fields should be identical. – Spacedman Mar 5 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.