postgresql partition check on hstore field

Question

i have a master table that i am partitioning. everything is fine, except that one of the fields is a hstore type and i would like the CHECK on the partition creation to check against that hstore key's value:

CREATE TABLE master_table 
( 
  id SERIAL, 
  created_at TIMESTAMP WITH TIME ZONE NOT NULL, 
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL, 
  data HSTORE DEFAULT hstore(array[]::varchar[])
);

and for the partitions:

CREATE TABLE partition_field1 
(
  CHECK data->'field' = 1 
) INHERITS ( master_table );

however, i get the following error:

ERROR:  syntax error at or near "data"
LINE 1: CREATE TABLE partition_field1 ( CHECK data->'field...
                                              ^

is this possible? or do i have to specify the hstore key appropriately?

Answer 1

You forgot the parens on the CHECK constraint. I'd use the full CONSTRAINT syntax:

CREATE TABLE partition_field1 
(
  CONSTRAINT data_field_equals_1 CHECK (data->'field' = 1)
) INHERITS ( master_table );