Cast column to integer with ogr2ogr

Question

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?

Answer 1

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.

Answer 2

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