index unique

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

Responses

Browse pgsql-general by date

  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