Re: How to get the size of JSONB in bytes?

Hi,

Petr, Jim, thank you for suggestions and thoughts. Now I see, that you
can't cast 'jsonb' to 'bytea' directly, but you can do it through
'text'. I modified my trigger like this

create function check_document() returns trigger as $$
begin
if 10240 < octet_length(new.jdoc::text::bytea) then
raise exception 'Document is too big';
end if;
return new;
end
$$ language plpgsql;

and now it works! I think they should add casting to 'bytea' directly,
such workarounds shouldn't be necessary.

As for what the right place for data validation is, I like it to be as
close to the data as possible. As long as my checks are not very
complicated, I prefer them to be on the database level.

Best regards,
Dmitry.

On Wed, Dec 16, 2015, at 06:52 AM, Jim Nasby wrote:
> On 12/15/15 8:24 AM, Petr Korobeinikov wrote:
> > The better approach is extract your length-validation logic into your
> > application.
>
> That's really up to interpretation.
>
> The database is the only place the data is stored, and as such is the
> only place that can constrain that data in all places.
>
> If you're accepting data from a web form or something you certainly want
> it to also check things, so the user gets immediate feedback. But for
> anything you need to guarantee, you need to use the database.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com