Обсуждение: How to get the size of JSONB in bytes?
Hi,
I want to impose size restrictions on JSONB documents stored in my table. Say, no document over 10Kb must be inserted in the table. For that, I try to write a trigger like this (jdoc here is of JSONB type):
create function check_document() returns trigger as $$
begin
if 10 * 1024 < octet_length(new.jdoc::bytea) then
raise exception 'Document is too big';
end if;
return new;
end
$$ language plpgsql;
create trigger check_document_trg
before insert or update
on documents
for each row
execute procedure check_document();
This doesn't work because it can't cast JSONB to 'bytea'. I tried casting to 'text', still no luck. Could anyone please help me?
Also, is there a better approach to impose a size limit, then writing triggers? I need it to be fairly flexible, e.g. 10Kb is not a constant, it may even be different for different documents (rows) in the same table.
Best regards,
Dmitry.
This doesn't work because it can't cast JSONB to 'bytea'. I tried casting to 'text', still no luck. Could anyone please help me?
You can use check-constraint like this:
# create table t (
jb jsonb
);
# alter table t add constraint jb_length_check CHECK (length(jb::text) < 16); -- 16 is example value
# insert into t values ('{"key":"v"}');
INSERT 0 1
# insert into t values ('{"key":"value"}');
ERROR: new row for relation "t" violates check constraint "jb_length_check"
DETAIL: Failing row contains ({"key": "value"}).
Also, is there a better approach to impose a size limit, then writing triggers? I need it to be fairly flexible, e.g. 10Kb is not a constant, it may even be different for different documents (rows) in the same table.
The better approach is extract your length-validation logic into your application.
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
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
and now it works! I think they should add casting to 'bytea' directly,
such workarounds shouldn't be necessary.
Casting to bytea and counting the bytes is just as hackey, IMO. If this use-case wants to be officially supported the type itself should define what "length/size" means and a function should be provided to extract that value. I have the impression that the content length is already part of the stored data so really only the function would be needed. I haven't given it that much thought, and zero present research, so I may be mis-remembering old threads.
David J.