Re: Partial index on JSON column - Mailing list pgsql-general

From Michael Lewis
Subject Re: Partial index on JSON column
Date
Msg-id CAHOFxGr7F3DVJkYPHOmNPpEYD==DvyYpzM8JTA2vrLrnQSRnNg@mail.gmail.com
Whole thread Raw
In response to Re: Partial index on JSON column  (Samuel Williams <space.ship.traveller@gmail.com>)
List pgsql-general


On Wed, 20 Feb 2019 at 10:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Samuel Williams <space.ship.traveller@gmail.com> writes:
> When I do this query:

> EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->>
> 'suggestion_id'::text)::integer = 26) AND what = 'suggestion_notification';

> It's slow. I need to explicitly add the NULL constraint:

Try it like

EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->>
'suggestion_id'::text) = '26') AND what = 'suggestion_notification';

I don't think we assume that CoerceViaIO is strict, and without that
the deduction that the value couldn't be null doesn't hold.  In any
case you're better off without the runtime type conversion: that
isn't doing much for you except raising the odds of getting an error.

For run-time safe comparisons of something stored in jsonb that I expect to only have booleans (and I don't trust input to always be valid), I have a utility function like this-

CREATE OR REPLACE FUNCTION public.util_to_bool( pInput TEXT )
RETURNS BOOL AS
$BODY$
BEGIN
RETURN pInput::BOOL;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;


This is probably not best practice however.

 

                        regards, tom lane

pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: Re: Partial index on JSON column
Next
From: Samuel Williams
Date:
Subject: Re: Partial index on JSON column