7

I'm trying to import a CSV file to PostGIS with a subset of columns and cast all columns to the correct data type:

C:\OSGeo4W\bin\ogr2ogr.exe -f "PostgreSQL" "PG: host=localhost user=postgres dbname=data" -lco OVERWRITE=YES -lco schema=public D:\folder\lines.csv -sql "SELECT cast(name as char) as name, cast(comment as char) as comment, cast(someid as INTEGER) as someid, cast(wkt as char) as wkt from lines"

char ERROR 1: Unrecognized typename char in CAST operator.

I've also tried text, TEXT, varchar, VARCHAR, char, CHAR.

Without casting any fields the table is imported but almost all fields are VARCHAR type.

What am I missing?

1
  • It's rare to want CHAR data since there aren't many fixed-width strings. Fixed-width WKT seems extremely unlikely.
    – Vince
    Apr 25, 2023 at 12:21

1 Answer 1

8

You are trying to cast into "char" that is not known by GDAL. If the source data are in CSV format then the default SQL dialect is OGR SQL, and by the documentation https://gdal.org/user/ogr_sql_dialect.html the supported CAST types are these:

  • boolean
  • character(field_length). By default, field_length=1.
  • float(field_length)
  • numeric(field_length, field_precision)
  • smallint(field_length) : 16 bit signed integer
  • integer(field_length)
  • bigint(field_length), 64 bit integer, extension to SQL92
  • date(field_length)
  • time(field_length)
  • timestamp(field_length)
  • geometry, geometry(geometry_type), geometry(geometry_type,epsg_code)

Try casting into supported type "character". Your cast will work also as you wrote it with the SQLite dialect, selected by adding -dialect SQLite. The supported datatype names can be found from the SQLite documentation https://www.sqlite.org/datatype3.html (3.1.1. Affinity Name Examples), but as documented "This table shows only a small subset of the datatype names that SQLite will accept" and CHAR works as well.

0

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.