Ways to limit the number / length of key-value pairs in PostgreSQL HStore?

Question

One of my database tables has an HStore meta column.

Some users wish to add arbitrary keys to the column (via REST API calls) to attach arbitrary user defined data.

My main concern is an excessivly large HStore entry and the potential for abuse (eg: store arbitrary base64 data or fill the database with garbage data).

Is there a mechanism in PostgreSQL (at the database layer- not the application layer) to limit the number of keys and length of entries in an HStore column?

Answer 1

Use a check constraint which limits the number of keys, e.g.:

create table my_table(
    meta hstore check (cardinality(akeys(meta)) < 3)
);

insert into my_table (meta) values
('a=>1, b=>2, c=>3');

ERROR:  new row for relation "my_table" violates check constraint "my_table_meta_check"
DETAIL:  Failing row contains ("a"=>"1", "b"=>"2", "c"=>"3").

You can also add a limit of the length of hstore values in their textual representation, e.g.:

create table my_table(
    meta hstore check (cardinality(akeys(meta)) < 3 and length(meta::text) < 200)
);