Re: index unique

Marc Millas schrieb am 03.06.2021 um 22:51:
> on a table we need a primary key and to get a unique combinaison, we need 3 columns of that table:
> 1 of type integer,
> 1 of type text,
> 1 of type geometry
>
> creating the PK constraint doesn work: (even with our current small data set)
> ERROR:  index row size 6072 exceeds btree version 4 maximum 2704 for index "xxx_spkey"
> DETAIL:  Index row references tuple (32,1) in relation "xxx".
> HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
> Consider a function index of an MD5 hash of the value, or use full text indexing.
>
> ok. we can do this.
> but if so, we need to create a gist index on the geometry column to do any topology request.
> so 2 indexes containing this single column.
>
> if we install extension btree_gist, no pb to create an index on all 3 columns.
> but as gist does not support unicity, this index cannot be used for the PK.
>
> OK, we may try to use a function to get the bounding box around the geometry objects and use the result into a btree
index........
>
> Any idea (I mean: another idea !) to tackle this ?
> Or any critic on the "solution" ??

How do you define the "uniqueness" of the geometry?

GIST can support "uniqueness" through exclusion constraints.
It's not a primary key, so you can't create foreign keys referencing that table,
but it does ensure uniqueness (In fact the "normal" unique indexes are
essentially a special case of exclusion constraints)

    create index on the_table using gist (int_column with =, text_col with =, geometry_col with &&);

Replace the && operator with whatever is appropriate for your use case.

Thomas