Re: looking for a globally unique row ID

W dniu 14.09.2017 o 10:57, George Neuner pisze:
> On Thu, 14 Sep 2017 09:45:59 +0200, Rafal Pietrak <rafal(at)ztk-rp(dot)eu>
> wrote:
>
>> Hello everybody,
>>
>> Can anybody help me find a way to implement an ID which:
>>
>> 1. guarantees being unique across multiple tables.
>>
>> 2. guarantees its uniqueness not only during INSERT, but also during the
>> lifetime of the database/application (e.i. during future UPDATES).
>>
>> 3. guarantees persistence of value across database backup/restore/upgrade.

Seeing the answers I feel, I should probably have added:

4. not necessarily guarantee "planetary wide" uniquness. Meaning:
backup/restore should instantiate those ID dupplication on the second
instance of the database.

>
> UUID is the obvious choice, but it does take a lot of space.

I was hoping for something like a database-scoped "primary key" - which
in particular does not need to be anything big.... provided the dataset
is small.

As far as I can tell, UUID is an ID, that is "simple/fast" to generate,
and has "extremally low" probability of collisions.

Instead I was looking for a "mechanizms/program-sql-idioms" which don't
have to be particularly efficient, but once generated, no matter what,
the uniqueness is asurred by the database. Including UPDATEs - e.i.
assignment of a completly new ID for a particular ROW.

But I understand I may quit searching - there is nothing "so simple".

>
> Something like this might do the job:
> http://rob.conery.io/2014/05/28/a-better-id-generator-for-postgresql/

I'll have a look, but this is not quite the tool I'm looking for.

>
>
> George
>
>
>

Thank you all for answers.

-R