> > On Wed, 20 Feb 2019 at 10:14, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Samuel Williams <space.ship.travel...@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 >> >
