index unique

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

index unique

Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Paul Ramsey
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Peter J. Holzer
In reply to this post by Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

David G Johnston
On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer <[hidden email]> 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 type geometry
>
> creating the PK constraint doesn work:

I find that if a natural primary key candidate is so complex, it is
usually better to use a surrogate key.

You make this sound like an either-or proposition, but personally it takes a very exceptional circumstance to forgo defining a unique natural key.  Whether I choose to supplement that with a surrogate key is a different matter altogether.

In this case identity would seem to make more sense using labels, not composition.  For instance, on a substance table I would have the identifier for water be "H20", and have the chemical composition of water be "H20" (not the best example...).  In this case ensuring uniqueness of the formula-to-label dependency would be trivial to implement and I probably would too - but in the case of geometry I'd just accept that using an index to do this would not be possible and, if I really needed reassurance of geometry uniqueness, I would do so in triggers.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: index unique

Peter J. Holzer
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Peter J. Holzer
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

David G Johnston
On Tuesday, June 8, 2021, Marc Millas <[hidden email]> 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 I’ve yet to see or think of a use case that would warrant even considering doing such, ignoring the fact that actually changing such a value is likely to be a non-starter from a compatability perspective.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: index unique

Tom Lane-2
In reply to this post by Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Thomas Kellerer-4
In reply to this post by Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Alban Hertroys-4
In reply to this post by Thomas Kellerer-4
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Marc Millas
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: index unique

Peter J. Holzer
CONTENTS DELETED
The author has deleted this message.