Le 14 septembre 2017 19:11:19 GMT+02:00, Rafal Pietrak <ra...@ztk-rp.eu> a écrit : > > >W dniu 14.09.2017 o 15:54, Merlin Moncure pisze: >> On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak <ra...@ztk-rp.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. >>> >>> an obvious candidate - a single SERIAL() (same serial) used in every >>> table that needs that ID does not guarantee (2). >> >> A shared sequence meets all of those requirements. I tend to prefer > >Not really. > >As I said, I'm not looking for performance or "fair probability" of >planetary-wide uniqueness. > >My main objective is the "guarantee". Which I've tried to indicate >referring to "future UPDATEs". > >What I mean here is functionality similar to "primary key", or "unique >constraint". Whenever somebody (application, like faulty application >IMPORTANT!) tries to INSERT or UPDATE a not unique value there (which >in >fact could possibly be generated earlier by UUID algorithms, or even a >sequence), if that value is among table that uses that (misterious) >"global primary key"; that application just fails the transaction like >any other "not unique" constraint failing.
I wrote something about this some years ago, this might do the trick for you, maybe with some adjustments depending on your schema. The main idea should help anyway. See: http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html (no, I never wrote the second part about fk :-/) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
