Hi, postgres 12 with postgis. 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" ??* *thanks,* Marc MILLAS Senior Architect +33607850334 www.mokadb.com
