in our db we use uuid for pk fields, now we have a new link table between two existing tables.
The existing tables are ‘projects’ and ‘principals’, the intermediate table has to be filled with the pk’s of both tables, which I can get easily with a sql statement. But that intermediate table also has a UUID pk.
Is it possible to generate the uuid for the intermediate table with a SQL statement.
Hi,
Does this link table need it’s own separate PK ? Can’t you make the 2 FK columns the PK ?
Or can the same project be linked multiple times to the same principal (and vice versa) ?
Anyway, PostgreSQL does come with a native UUID datatype but for the creation of UUID’s you need to add a ‘contrib’ module to your database.
For more information see http://www.postgresql.org/docs/9.0/stat … -uuid.html and http://www.postgresql.org/docs/9.0/stat … -ossp.html
Hope this helps.
Hi Robert,
I think you might be right and we don’t need that separate pk in this table, which makes it an easy fix
I’ll look into the links you gave for future reference.
Greetinx,
Jos
ROCLASI:
For more information see http://www.postgresql.org/docs/9.0/stat … -uuid.html and http://www.postgresql.org/docs/9.0/stat … -ossp.html.
Technical question Robert: any idea what they are referring to here?
PostgreSQL provides storage and comparison functions for UUIDs, but the core database does not include any function for generating UUIDs, because no single algorithm is well suited for every application.
Also, what algorithm is Servoy using?
Hi David,
I believe this wikipdia page explains the differences between the versions of UUID algorithms pretty well.
As for Servoy, I believe they use version 4 of the algorithm. But Johan or Jan Blok can tell you for sure.
Postgres site implies some UUID versions are more applicable to certain applications than others. This I don’t understand.
Hi David,
Postgres site implies some UUID versions are more applicable to certain applications than others
There are a lot of article on the net about using UUID or not for some kind of apps.
In theory, the regular random UUID creation algorithms make that UUID are not sorted by creation order like integers are.
So using UUID for PK involve that the db engine maintain indexes for PK, so for each new record db engine will have to insert new created UUID values somewhere in the existing PK’s index, instead of just appending new value in the index, (so inserting a new record will use more cpu than integer PK indexes because of index reorganisation)
So if your application is inserting a lot of records/seconds in a table, UUID are not always considered as the best PKs to use for this table.
At the other side, UUID Pks help a lot if you need to synchronise or export data so UUID is really something to consider for each table feature and usage.
Wondering about UUID vs UUID.
Hi David,
I am not exactly sure what Peter Eisentraut (the author of that manual page) meant. From reading about the differences between the 5 versions of the UUID algorithm I can see why would one would choose one over the other but to say in what application you would use what I can’t say.
You could argue that you should always use the latest versions (4 or 5, with or without using namespaces as salt) because they seem to be more secure and even more less prone to collisions but perhaps there are applications where this might be too expensive (CPU load or otherwise) or something, I really don’t know.
But if you want you can email Peter and ask, his email is on the page.
One thing I did found out after going to the PostgreSQL IRC channel (freenode.net, #postgresql) to ask about this is that the OSSP library doesn’t work on all platforms (FreeBSD, perhaps more). This explains why this functionality is not in the core but provided as a contrib.
The community member (nickname: RhodiumToad) who told me this did write a separate FreeBSD UUID implementation to circumvent this limitation on FreeBSD.
Perhaps Jan or Johan can shed some light on the different applications.
ROCLASI:
OSSP library doesn’t work on all platforms (FreeBSD, perhaps more)
That certainly makes a lot more sense as a reason.
We’ve been moving everything over to UUID last few months for the reasons erdione listed. We’ve run into a few learning spots (ie. the fact that it is not a string object in spite of its appearance, value lists where the stored value is a UUID need to be handled a bit differently, etc) so just being extra curious.
Good stuff, thanks for the time.
david:
the fact that it is not a string object in spite of its appearance
Well you can store it as a string (varchar(26)) if you want to. This might make it also more portable across other sql vendors if you care about that.
We store as varchar. But with the Servoy UUID flag turned on when you retrieve the value programatically it is a UUID object at that point.
servoy just uses the default java one: