I need to have a constraint to assure two different rows have not the same values for two varchar columns, and there is no overlapping with the composed range with another two columns.
The table is created as:
create table test (id varchar(255) not null,
amount_from float8,
amount_to float8,
company varchar(255) not null,
currency varchar(255) not null,
primary key (id));
Also, I've created a custom type as:
CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi);
Now, I can create a EXCLUDE constraint to avoid overlapping with the composed ranges in base to the amounts columns (amount_from and amount_to).
alter table test_rule add constraint AMOUNTS_RANGES_OVERLAPING
EXCLUDE USING gist (floatrange(amount_from, amount_to, '[]') with &&)
But I'm not abled to include in that contraint that the company and currency columns must be equal. Something like this:
alter table test_rule add constraint AMOUNTS_RANGES_OVERLAPING
EXCLUDE USING gist (floatrange(amount_from, amount_to) with &&,
currency with =,
company with =)
This sentence provokes the following error:
SQL Error [42704]: ERROR: data type text has no default operator class for access method "gist"
Hint: You must specify an operator class for the index or define a default operator class for the data type.
Gist is not able to handle text types.
How can I include these columns to complete the expected constraint? Is there any function or type conversion to allow the text comparation?
Or, perhaps, is there a way to use a double EXCLUDE clause to use gist and btree to obtain what I need? Something in this way:
alter table test_rule add constraint AMOUNTS_RANGES_OVERLAPING
EXCLUDE USING btree (currency with =, company with =)
EXCLUDE USING gist (floatrange(amount_from, amount_to) with &&)