Re: Cast jsonb to numeric, int, float, bool

From: Darafei Praliaskouski <me(at)komzpa(dot)net>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Cast jsonb to numeric, int, float, bool
Date: 2018-02-28 21:43:08
Message-ID: 151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: not tested

We're using this patch and like it a lot.

We store a lot of log-like data in s3-hosted .json.gz files.
Sometimes we need to suddenly ingest them and calculate statistics and check the new what-ifs.
We ingest data to simple single-column table with jsonb field, and then perform our calculation on top of it.
Without this patch the only option to get data already parsed as numbers into jsonb into calculation is via binary-text-binary transformation.

We're relying on the patch for our custom spatial type extension and casting in it.
https://github.com/gojuno/lostgis/blob/master/sql/types/type_tpv.sql#L21

For postgres installations without the patch we do WITH INOUT cast stubbing,
https://github.com/gojuno/lostgis/blob/master/sql/types/__jsonb_casts.sql
- but without performance benefits of raw C processing :)

A thing this patch does not implement is cast from jsonb to bigint.
That would be useful for transforming stored osm_id OpenStreetMap object identifiers.
Right now we're stubbing it with jsonb::numeric::bigint cast, but the middle one would be nice to get rid of.

The new status of this patch is: Ready for Committer

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2018-02-28 21:43:11 RFC: Add 'taint' field to pg_control.
Previous Message Tomas Vondra 2018-02-28 21:07:16 Re: Online enabling of checksums