From: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | index unique |
Date: | 2021-06-03 20:51:55 |
Message-ID: | CADX_1aYbmMHK4wqHd=p_fGsNMOgEB7oe0BY_9ZTWv3e-TQx+oA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Millas | 2021-06-03 20:55:34 | Re: syntax question |
Previous Message | David G. Johnston | 2021-06-03 20:22:58 | Re: syntax question |