Opened 15 years ago

Closed 15 years ago

Last modified 14 years ago

#1009 closed defect (fixed)

boolean, array types not handled reasonably on PostGIS layers

Reported by: smizuno Owned by: jef
Priority: major: does not work as expected Milestone:
Component: Data Provider Version: Trunk
Keywords: PostGIS boolean array Cc:
Must Fix for Release: Yes Platform: All
Platform Version: Awaiting user input: no


A PostGIS table containing a boolean or any array type (also some other types) causes an empty Attribute table (rows/columns, but no data) and "No features found" using Identify. This worked in 0.8.1.

The boolean type is often quite useful.

The problem is that a cast to text is used when building the binary cursor query in QgsPostgresProvider, select() and getFeatureAtId() functions, but there are no such casts to text in PostgreSQL for these types, so the query causes a database error, which is not reported to the user.

Reasonable handling would be to use the default string handling in QGIS for any type not specifically handled. This works as long as there is a textual return from the database like there is with a non-binary cursor. Even editing will work.

Interestingly, PostGIS provides a boolean to text function, so a boolean type column could be requested using text(boolean_column) rather than boolean_column::text.

A work-around is to create suitable type-to-text functions and casts on the database so the ::text cast is available for these types.

I am using PostgreSQL 8.2.6 / PostGIS 1.3.2

Attachments (1)

patch_for_bug_1009.txt (1.3 KB ) - added by smizuno 15 years ago.

Download all attachments as: .zip

Change History (7)

comment:1 by jef, 15 years ago

Owner: changed from nobody to jef

comment:2 by jef, 15 years ago

Resolution: fixed
Status: newclosed

fixed in r8285. Please test.

in reply to:  2 comment:3 by smizuno, 15 years ago

Resolution: fixed
Status: closedreopened

Replying to jef:

fixed in r8285. Please test.

I have tested with r8290 and it works as expected for boolean type. Array and money types just show blank cells where there should be values. Even NULL is not displayed for null values.

Since I posted this defect I have come up with a possible solution for handling types that don't have a cast to text. This should fit with the revised PostGIS provider. However, this may have PostgreSQL version differences as the functions are not published in the documentation.

Use a function that outputs a cstring for a specified type. I believe that the return from the query is actually a cstring, so there is no need to get the text type. Also be sure to use a column alias to identify the column.


boolean type: boolout("column_name") as "column_name"

money type: cash_out("column_name) as "column_name"

array type: array_out("column_name") as "column_name"

array_out should work for any array type, but I have tested only with integer (int4, int8) and text arrays. There is also an anyarray_out() function, but I don't see any significant difference.

If there is a problem handling cstrings the output functions above can be wrapped with a textin() function to get to a text type. I don't believe that there is such a problem, but different versions of PostgreSQL may have slightly different behavior.

I have attached a patch incorporating the ...out() functions for money, array, and boolean types.

I have tested (but not extensively) the patch on boolean and money types, and integer and text arrays. Identify, Attribute table (display and edit), Feature editing all work.

by smizuno, 15 years ago

Attachment: patch_for_bug_1009.txt added

comment:4 by smizuno, 15 years ago

I'm sorry - please disregard the part about NULL is not displayed for null values. I rechecked the types and found that NULL is displayed.

comment:5 by jef, 15 years ago

Resolution: fixed
Status: reopenedclosed

comment:6 by (none), 14 years ago

Milestone: Version 0.9.2

Milestone Version 0.9.2 deleted

Note: See TracTickets for help on using tickets.