PostGIS – OGR2OGR Error Importing Shapefile: ‘Numeric Field Overflow’

ogr2ogrpostgisshapefile

I'm trying to import a shapefile into a PostGIS database using ogr2ogr as follows:

ogr2ogr -lco GEOMETRY_NAME=geom -lco LAUNDER=NO -a_srs "EPSG:4326" -f "PostgreSQL" PG:"dbname=db" test.shp

This is after trying the import with shp2pgsql and encountering an issue OGR integer type to PostgreSQL BIGINT?.
The shapefile was a Tiger/Line file circa 2000 if I remember correctly. But I am getting the following error:

ERROR 1: COPY statement failed.
ERROR:  numeric field overflow
DETAIL:  A field with precision 19, scale 11 must round to an absolute value less than 10^8.
CONTEXT:  COPY test, line 1, column TLID: "142691303.00000000000"

The TLID field in question shows as double real 18 11 for type, type name, length and precision in QGIS 3/2.99. I did some quick search, and TLID seems to be TIGER/Line ID. I don't why it is shown as a double since it's an ID, or why is it causing this error for ogr2ogr.

This is tested under Ubuntu 16.04, and ogr2ogr --version shows:

GDAL 2.2.1, released 2017/06/23

Does anyone know what went wrong and how to fix this to allow importing into PostGIS?

— EDIT —

The dbfdump -m output for the file is:

Record: 0
STATEFP: 06
COUNTYFP: 037
COUNTYNS: (NULL)  
TLID: 142691303.00000000000
TFIDL: 219999921.00000000000
TFIDR: 219999382.00000000000
MTFCC: S1400
FULLNAME: Fashion Ave                                                                                         
SMID: 2361                  
LFROMADD: (NULL)      
LTOADD: (NULL)      
RFROMADD: (NULL)      
RTOADD: (NULL)      
ZIPL: (NULL)
ZIPR: (NULL)
FEATCAT: (NULL)
HYDROFLG: N
RAILFLG: N
ROADFLG: Y
OLFFLG: N
PASSFLG: (NULL)
DIVROAD: N
EXTTYP: N
TTYP: (NULL)
DECKEDROAD: N
ARTPATH: (NULL)
Shape_Leng:      42.48449176210

Record: 1
STATEFP: 06
COUNTYFP: 037
COUNTYNS: (NULL)  
TLID: 141692965.00000000000
TFIDL: 219999899.00000000000
TFIDR: 219999383.00000000000
MTFCC: S1630
FULLNAME: (NULL)                                                                                              
SMID: 2361

Best Answer

For making the conversion to success read the manual page http://www.gdal.org/drv_pg.html from section Layer Creation Options

PRECISION: This may be "YES" to force new fields created on this layer to try and represent the width and precision information, if available using NUMERIC(width,precision) or CHAR(width) types. If "NO" then the types FLOAT8, INTEGER and VARCHAR will be used instead. The default is "YES".

In your shapefile the field definition and data do not match but you should be able to workaround that by unsetting the "precision" in your org2ogr command

-lco precision=NO
Related Question