Error in update trigger with json/geometry columns

[sylvere-wexity]

With postgres 9.5 and hasura 1.0-alpha44, when updating a row with a json (or geometry) column, the trigger function throws the following error :

ERROR:  could not identify an equality operator for type json
CONTEXT:  PL/pgSQL function hdb_views."notify_hasura_test_trigger_UPDATE"() line 39 at IF

Error seems to come from comparison between current and previous row values.

[tirumaraiselvan]

@sylvere-wexity Thanks for reporting the error!

A quick fix is to use jsonb column instead of json since jsonb has an equality operator. You can modify the column type via the console as well.

In case you want to ignore changes to the json column but only care for updates to other columns, then you can unselect the json column in the Listen columns configuration (in the Advanced Settings) of your event trigger. Note, you will still receive the entire row in the payload.

For geometry, the equality operator is not the standard = but ST_Equals. We will figure out how to do column type specific comparison in a (near) future release.

[michaelhayman]

Any update? This bug bit me today, was expecting this to work with Point geometry type. :|

My workaround for now is adding an an extra jsonb column for now with lat/lng and duplicating the data.

[marionschleifer]

We will release a fix for this by the end of this week 🙂