I use ogr2ogr to import an .sqlite file into my PostgreSQL database. The new database table I get has a column of type "character varying". However I want it be of type integer as all the values in the particular column are integer numbers with the exception of some empty fields. How do I do this?

  • Is that field reported as string or integer by ogrinfo ogrinfo -al -so my_db.sqlite?
    – user30184
    Commented Apr 19, 2018 at 6:53
  • @user30184 It is reported as string. String (0.0) to be more precise.
    – Elmex80s
    Commented Apr 19, 2018 at 7:11
  • What is your GDAL version?
    – user30184
    Commented Apr 19, 2018 at 8:05
  • @user30184 gdal-config --version gives 2.2.2.
    – Elmex80s
    Commented Apr 19, 2018 at 9:49

2 Answers 2


If you want to do it in the ogr2ogr -sql parameter then you want to use the cast(<expr> AS <type-name>) syntax - outlined in the 'CAST expressions' section here.

eg: SELECT cast(a.my_text_field AS int), a.geometry FROM mytable a etc

If you want to do it in Postgres then you can use the same syntax or the :: syntax.

If this isn't working post an example of your data and the code you've been trying.

  • just to add, the :: cast shorthand syntax isn't recognised by the ogr2ogr inbuilt SQL interpreter. Nor does it recognise any functions from extensions. For example, ST_GeomFromText PostGIS function will result in a ERROR 1: Undefined function 'ST_GeomFromText' used. error.
    – Theo F
    Commented Apr 17 at 16:01
  • I often need to add -dialect sqlite. E.g.: ogrinfo -sql "select sum(bijeen) totaalbijeen from vbo_nl_pc_5_10" vbo_nl_pc_5_10.dbf does NOT work, gives: ERROR 1: Column Summary Function 'SUM' found in an inappropriate context. ogrinfo -dialect sqlite -sql "select cast(sum(bijeen) as integer) totaalbijeen from vbo_nl_pc_5 _10" vbo_nl_pc_5_10.dbf works fine.
    – Jan
    Commented May 16 at 9:24

I couldn't get this casting to work (I'm using the OCI driver to pull from an Oracle data source and write to a .csv), but used a workaround: a CASE statement

The problem is that the data I'm pulling from contain many nulls in these fields, and in some cases, one of the fields is 100% nulls.

Ex.1 Tried to cast, but it didn't work when writing to CSV. I think this is the limitation of a CSV file itself (I don't think it can store field types at all).

select CAST(s.slope AS int) as IAPP_slope, CAST(s.aspect AS int) as IAPP_aspect, CAST(s.elevation AS int) as IAPP_elev, from invasive_plant_site s

Ex. 2 So instead, I use case statements to just write dummy int values as outputs when I encounter a null value, to ensure I have a clear-cut integer value in that field. I use 9999 as it's not an actual value I would encounter for any of these fields:

select case when s.slope is null then 9999 else s.slope end as IAPP_slope, -- force int field types via dummy value 9999 case when s.aspect is null then 9999 else s.aspect end as IAPP_aspect, case when s.elevation is null then 9999 else s.slope end as IAPP_elev, from invasive_plant_site s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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