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
