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
