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.
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.