'infinity' PostgreSQL date/time input not showing in QGIS

Question

I have a PostgreSQL table with a date-type column populated either with proper dates (e.g. 2016年01月01日) or with PostgreSQL infinity date/time input.

When I display the table in QGIS 2.14.11-1 (via DB Manager), the rows containing infinity values display NULL values instead (see last row on screenshot below).

enter image description here

Therefore, I'm unable to apply a rule-based style for these rows that remain impossible to select via the following rules (both rules return 0 selected feature):

"date" IS NULL
"date" = NULL
"date" = 'infinity'

Any idea on this issue and how I could deal with it?

Answer 1

Work around

You may be able to create a VIEW that wraps the table.

CREATE VIEW foo AS
SELECT 
 gid,
 insee,
 libelle,
 statut_date,
 CASE
 WHEN isfinite(fusion_Date)
 THEN fusion_date
 ELSE '3000-01-01'::date
 END AS fusion_date
FROM table;

File a bug

• https://hub.qgis.org/projects/quantum-gis/issues

• wiltomap: Thanks @EvanCarroll! This is a good option but the best would be to be able to deal with infinity values within QGIS. I'm thinking about filing a bug then.
• Evan Carroll: That's all you can do. I would guess this would be a very low priority bug. I don't think I've ever seen anyone store infinity in a table.. I would love to know what your use case is for it, just because this is the first time I've seen it. Even PostgreSQL ranges can't operate on infinity. It's like a value that just sits there so you can't compare to an upper-bound that you can store as another time.
• wiltomap: See here: dba.stackexchange.com/q/149076/48623 for my need to use infinity.
• Evan Carroll: @wiltomap that's bad advice. Do you want me to submit a new answer. ;)