On 2021-06-11 14:37:57 +0200, Marc Millas wrote:
> anyway, my original post was about the fact that we must create a PK based on
> some kind of work around the limited btree length behaviour thus creating an
> index that will never be of any use, just for technical reasons.
Actually, that index
Hi Alban,
I plainly agree on the uniqueness thing. and on the fact that a PK with
only a geometry column can be considered somehow "ill suited".
That said, the PK we finally use contains, as said, 3 columns:
--an id (integer column)
--a topology describer (the text column)
--and the geometry
> On 8 Jun 2021, at 22:50, Thomas Kellerer wrote:
>
> 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
>>
>
> How do you define
Thanks Thomas,
but, as stated after the first post, the need was for a PK as asked by
postgres (ie. for tech needs, not for functionnal needs)
up to now, looks like we must create a PK (and so, the associated index)
just to answer logical replication needs.(and qgis which also needs a PK)
that
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
Marc Millas writes:
> I know, its clearly stated in postgres doc about btree," The only
> limitation is that an index entry cannot exceed approximately one-third of
> a page (after TOAST compression, if applicable)."
Yup.
> is there any plan to adress this ?
No. The cost/benefit ratio seems
On Tuesday, June 8, 2021, Marc Millas wrote:
>
> but as this looks quite hard coded, it means that for long utf8 things
> the data length is not so long before hitting the limit.
>
> is there any plan to adress this ?
>
None that I’ve seen, and I don’t expect to see one either. Mainly because
Cristal clear !
and it have to be the case as my test was done with some not so random data.
but this mean that we cannot put a bunch of datatypes in a PK, as soon as
it may be longer than 2701.
I know, its clearly stated in postgres doc about btree," The only
limitation is that an index entry
On 2021-06-08 18:30:16 +0200, Marc Millas wrote:
> the only pb observed is the size of the object accepted. if the geom is a bit
> "big" then the index errors.about btree size of index object.
> but if I create a table test_l with a text column blabla as a PK, and insert a
> 100 000 character long
quite funny to see how a tech question seems to end into an english grammar
thing :-)
quote
> You make this sound like an either-or proposition,
While he is talking about *a* primary key, it should be *the* primary
key. There can be only one (that's why it is the primary key).
There can be
On 2021-06-07 10:20:22 -0700, David G. Johnston wrote:
> On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer wrote:
> On 2021-06-03 22:51:55 +0200, Marc Millas wrote:
> > postgres 12 with postgis.
> > on a table we need a primary key and to get a unique combinaison, we
> need
> > 3
On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer wrote:
> On 2021-06-03 22:51:55 +0200, Marc Millas wrote:
> > 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
On 2021-06-03 22:51:55 +0200, Marc Millas wrote:
> 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:
I find that
Hi Paul,
obviously the bounding box is not a perfect choice. we also think of one of
the "centers" point, but quite similar non uniqueness.
so, if no "tech" solution, we continue to work with the business to try to
find an appropriate PK
thanks
Marc MILLAS
Senior Architect
+33607850334
Primary key is going to be a BTREE index. I'm surprised you require the
geometry in order to achieve uniqueness?
You can't put the geometry into a BTREE because it's too large.
You could add a column and stick the MD5 hash of the geometry there, and use
that as the last piece of uniqueness?
If
15 matches
Mail list logo
