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?
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.
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
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