# create UUID with sql statement on Postgres

**URL:** <https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341>\
**Category:** Classic Servoy\
**Created:** [July 22, 2011, 3:27pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341 "2011-07-22T15:27:02Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![jdbruijn](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@jdbruijn](https://forum.servoy.com/u/jdbruijn)\
**Post date:** [July 22, 2011, 3:27pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/1 "2011-07-22T15:27:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [July 22, 2011, 5:00pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/2 "2011-07-22T17:00:41Z")

</div>

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](http://www.postgresql.org/docs/9.0/static/datatype-uuid.html) and [http://www.postgresql.org/docs/9.0/stat … -ossp.html](http://www.postgresql.org/docs/9.0/static/uuid-ossp.html)

Hope this helps.

---

<div class="post-metadata">

**Author:** ![jdbruijn](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@jdbruijn](https://forum.servoy.com/u/jdbruijn)\
**Post date:** [July 22, 2011, 5:35pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/3 "2011-07-22T17:35:19Z")

</div>

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 ![:D]( "Very Happy")

I’ll look into the links you gave for future reference.

Greetinx,

Jos

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [July 23, 2011, 6:54pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/4 "2011-07-23T18:54:11Z")

</div>

> ROCLASI:  
> For more information see [http://www.postgresql.org/docs/9.0/stat … -uuid.html](http://www.postgresql.org/docs/9.0/static/datatype-uuid.html) and [http://www.postgresql.org/docs/9.0/stat … -ossp.html](http://www.postgresql.org/docs/9.0/static/uuid-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?

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [July 24, 2011, 7:22pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/5 "2011-07-24T19:22:40Z")

</div>

Hi David,

I believe [this wikipdia page](http://en.wikipedia.org/wiki/UUID) 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.

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [July 24, 2011, 8:05pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/6 "2011-07-24T20:05:13Z")

</div>

Postgres site implies some UUID versions are more applicable to certain applications than others. This I don’t understand.

---

<div class="post-metadata">

**Author:** ![erdione](https://avatars.discourse-cdn.com/v4/letter/e/e9a140/32.png) [@erdione](https://forum.servoy.com/u/erdione)\
**Post date:** [July 25, 2011, 7:35am UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/7 "2011-07-25T07:35:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [July 25, 2011, 11:05am UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/8 "2011-07-25T11:05:02Z")

</div>

Wondering about UUID vs UUID.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [July 25, 2011, 11:49am UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/9 "2011-07-25T11:49:27Z")

</div>

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](http://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](http://blog.rhodiumtoad.org.uk/2008/12/21/uuid-generation-for-postgresql-83-on-freebsd/) to circumvent this limitation on FreeBSD.

Perhaps Jan or Johan can shed some light on the different applications.

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [July 25, 2011, 1:42pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/10 "2011-07-25T13:42:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [July 25, 2011, 2:11pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/11 "2011-07-25T14:11:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [July 25, 2011, 2:29pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/12 "2011-07-25T14:29:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\
**Post date:** [July 25, 2011, 3:32pm UTC](https://forum.servoy.com/t/create-uuid-with-sql-statement-on-postgres/14341/13 "2011-07-25T15:32:26Z")

</div>

servoy just uses the default java one:

[http://download.oracle.com/javase/1,5.0 … UUID%28%29](http://download.oracle.com/javase/1,5.0/docs/api/java/util/UUID.html#randomUUID%28%29)
