Re: Casting json (or jsonb) to real

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Casting json (or jsonb) to real
Date: 2022-06-01 01:08:54
Message-ID: CAOC+FBVgADFvgpwTeSB=vU=Lv2ea_HvJZdZSZr5A7B=9d_p0Ww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Well, it's because the representation of the value is getting double
quoted, so it's

select '"1.453"'::real, which yields ERROR: cannot cast jsonb string to
type real.

I have all this serialized string data stored in the jsonb col, all of
which is double quoted.

On Tue, May 31, 2022 at 5:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Wells Oliver <wells(dot)oliver(at)gmail(dot)com> writes:
> > When I know this value is a floating point value I would just like to
> cast
> > SELECT value::real but you can't do that. You can do SELECT
> > (jsonbcol->>'somekey')::real, but these columns just store a value.
>
> Works for me, in v11 and up:
>
> regression=# select '1.4531'::jsonb::real;
> float4
> --------
> 1.4531
> (1 row)
>
> In older versions you could do
>
> regression=# select '1.4531'::jsonb::text::real;
> float4
> --------
> 1.4531
> (1 row)
>
> I fear that either one involves a conversion to text and back
> under the hood :-(. Now, if you were casting to numeric,
> that would be very cheap.
>
> regards, tom lane
>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Wells Oliver 2022-06-01 01:14:28 Re: Casting json (or jsonb) to real
Previous Message Tom Lane 2022-06-01 00:50:51 Re: Casting json (or jsonb) to real