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
    Apr 19, 2018 at 6:53
  • @user30184 It is reported as string. String (0.0) to be more precise.
    – Elmex80s
    Apr 19, 2018 at 7:11
  • What is your GDAL version?
    – user30184
    Apr 19, 2018 at 8:05
  • @user30184 gdal-config --version gives 2.2.2.
    – Elmex80s
    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.


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 that you have read and understand our privacy policy and code of conduct.

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