Create check constraint on HSTORE to maintain specific succession of data

Question

Question is regarding check constraint on HSTORE field in Postgres.

create table archives_seasonmodel
    (episodes      hstore)

This is very shortened version of table I have, but just for example its ok.

Episodes contains data in the following format:

{ 
1 => 2020-03-01, 2 => 2020-03-07,  5=> 2020-03-29, 3=> 2020-03-14
}

Where key is always a positive digit and value is a date.

I want to create a constraint that would check any new data for following condition:

– each date in a key/value pair should be greater or equal comparative to previous key/value pair ordered by keys ASC.

Good data:

{ 
1 => 2020-03-01, 2 => 2020-03-07,  5=> 2020-03-29, 3=> 2020-03-14
}
2020-03-29 >= 2020-03-14 >=   2020-03-07 >=  2020-03-01

5 >=3 >=2 >=1

Bad data:

{ 
1 => 2020-03-01, 2 => 2020-06-07,  5=> 2020-03-29, 3=> 2020-03-14
}
2020-03-29 >= 2020-03-14 not >=   2020-06-07 >=  2020-03-01

5 >=3 >=2 >=1

2020-03-14 not >= 2020-06-07 but it should be as 2020-03-14 has key 3 2020-06-07 has key 2. Date of key 3 should be greater or equal to date with key 2 because 3 > 2.

Is it possible to create such constraint or it is just out of reality???

Thank you

Answer 1

Create a function for checking the condition. Use the hstore function each() and aggregation function array_agg().

create or replace function check_episodes(hstore)
returns boolean language sql as $$
    select array_agg(key order by key::int) = array_agg(key order by value::date)
    from each($1)
$$;

create table archives_seasonmodel (
    episodes hstore check (check_episodes(episodes))
);

Db<>fiddle.