Opened 14 years ago

Closed 14 years ago

Last modified 14 years ago

#3738 closed defect (fixed)

pg driver does not retrieve field size from a sql statement

Reported by: medspx Owned by: chaitanya
Priority: high Milestone:
Component: OGR_SF Version: 1.6.3
Severity: normal Keywords: postgresql, pg, field, size
Cc: Daniel Morissette

Description

Hi,

When you try to export some data from a postgis database to MapInfo File format with ogr2ogr and an sql statement, the size of the fields are always the defaults one (254 for characters) even if you specified a field size in the sql statement.

e.g.: ogr2ogr -f "MapInfo File" test.tab PG:"host=localhost dbname=test" -sql "SELECT cast(field_1 as character(80)), wkb_geometry FROM table WHERE field_1='xxxxxx'" returns a test.tab with a field_1 field which size is 254 and not 80.

If you do the same thing with Esri Shapefile Driver, it is good: ogr2ogr -f "Esri Shapefile" test.shp PG:"host=localhost dbname=test" -sql "SELECT cast(field_1 as character(80)), wkb_geometry FROM table WHERE field_1='xxxxxx'" returns a test.shp file with a field_1 field with a size of 80 characters.

Seems that there is a bug in the mitab driver which do not take care of field size even if it is defined in the sql statement. It is true for tab and mif/mid formats...

Change History (7)

comment:1 by warmerdam, 14 years ago

Cc: Daniel Morissette added
Component: defaultOGR_SF
Owner: changed from warmerdam to chaitanya

Chaitanya,

Could you confirm this problem, and take a peek to see what is going on. It seems odd that the PG driver would be properly returning the field sizes for the ExecuteSQL() result fields, but the mapinfo driver not use them if it uses requested field sizes in other contexts.

comment:2 by chaitanya, 14 years ago

Status: newassigned

comment:3 by chaitanya, 14 years ago

Keywords: postgresql pg added; mapinfo mitab removed
Resolution: fixed
Status: assignedclosed

The problem here is that the pg driver can't report the width of a column in an SQL query result.

PQfsize() gives -1 for a column defined as char(n) when it is used in an SQL statement. Using the CAST() operator doesn't make any difference.

depesz from the PostgreSQL IRC says that char() is technically same as text. text type doesn't have a set width.

The size of the char(n) column is listed as -1 in the table definition table. While reading the table definition, the pg driver reads the length from the table definition. We can't really do this with a query result in a consistent manner.

I couldn't find any work around for this. We have to be satisfied with the default length in this case.

BTW, medspx, the default size of a text field in the Shapefile format driver is 80.

comment:4 by chaitanya, 14 years ago

medspx,

You can use the -dialect option to set the SQL dialect to OGRSQL in ogr2ogr. OGR's SQL processor retains the width information of a column in SQL result from the base table. This option is available from the 1.7 branch of gdal.

comment:5 by chaitanya, 14 years ago

Summary: mitab driver does not take field size from a sql statementpg driver does not retrieve field size from a sql statement

comment:6 by Even Rouault, 14 years ago

Chaitanya,

I'm surprised by your statement that the PG driver cannot retrieve the column with from a SQL result layer. Indeed it can since my fix in r18653 for #3349

For example : ogrinfo PG:dbname=autotest -sql "select cast(eas_id as numeric(34,21)) as col1, cast('a' as character(50)) as col2 from poly limit 1"

returns:

INFO: Open of `PG:dbname=autotest'
      using driver `PostgreSQL' successful.


{{{
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
col1: Real (34.21)
col2: String (50.0)
OGRFeature(sql_statement):0
  col1 (Real) =          168.000000000000000000000
  col2 (String) = a 
}}}

Doesn'it work for you ?

comment:7 by chaitanya, 14 years ago

It does work for me. I forgot to check the trunk version without the -dialect OGRSQL option.

medspx, you have to wait until 1.8 is released to get this functionality or use the 1.7 version with the -dialect OGRSQL switch.

Note: See TracTickets for help on using tickets.