1

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?

2

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

  • 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. – wiltomap Jan 17 '17 at 12:11
  • 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. – Evan Carroll Jan 17 '17 at 12:22
  • 1
    See here: dba.stackexchange.com/q/149076/48623 for my need to use infinity. – wiltomap Jan 17 '17 at 12:42
  • @wiltomap that's bad advice. Do you want me to submit a new answer. ;) – Evan Carroll Jan 17 '17 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.