UUID SHORT() for PostGres

Question

Hi I have a development built on MYSQL and considering on moving to PostGres due to the license costs for commercial software.

In my MYSQL I have multiple tables where I rely on the use of the UUID_SHORT() function as primary key in these tables. See link: Mysql UUID_SHORT Info

Does PostGres have a similar function to UUID_SHORT function? I am looking for a uniqiue ID and not interested in a sequence. Also something based on the system time like the UUID_SHORT would be awesome. Also below is the class function I use in PHP when I want to get a unique ID for primary key insert in MYSQL.

Appreciate any comments as this would be the main reason to or not transfer to PostGres.

function getUniqueId(){
        //return unique id

        $temp_id = strval(uniqid());
        $temp = $this->conn->prepare('CREATE TEMPORARY TABLE tempId (temp_id VARCHAR(30) PRIMARY KEY, id BIGINT UNSIGNED)');
        $temp->execute();
        $temp = $this->conn->prepare('INSERT INTO tempId(temp_id, id) VALUES(:temp_id, UUID_SHORT())');
        $temp->bindParam(':temp_id', $temp_id, PDO::PARAM_STR);
        $temp->execute(); 
        $temp = $this->conn->prepare('SELECT id FROM tempId WHERE temp_id = :temp_id ');
        $temp->bindParam(':temp_id', $temp_id, PDO::PARAM_STR);
        $temp->execute(); 
        $tempResult= $temp->fetchAll(PDO::FETCH_ASSOC);
        $temp = $this->conn->prepare('DROP TEMPORARY TABLE tempId');
        $temp->execute();
        $temp_id = $tempResult[0]['id'];     
        return $temp_id;
    }

I heard of a lot of reasons to switch, but never *that* one :p — Drew, Sep 12 '16 at 04:07
Postgres is a more fully featured database and it has a native UUID column type. Just use full-sized ones. It's probably not worth the hassle to use this mutant, non-standard "short" version. — tadman, Sep 12 '16 at 04:19
@Bohemian This might be in reference to [bundling MySQL with a paid application using the commercial license](http://www.mysql.com/about/legal/licensing/oem/). You can use MySQL as a back-end on a software-as-a-service platform for free, but if you're shipping it as part of an appliance you might need a commercial license. I'd ask a lawyer before paying anyone money, though. — tadman, Sep 12 '16 at 04:21
That "procedure" looks wildly complicated as well, but if you think it's strictly necessary that's exactly what stored procedures are for. — tadman, Sep 12 '16 at 04:23
Hey, I'll admit people abuse them all the time and they're a pain in the butt to maintain, but if this procedure for making a short UUID is used consistently and isn't likely to change in the future, a stored procedure is a lot neater and more predictable than four statements *per identifier*. — tadman, Sep 12 '16 at 04:43
also see http://stackoverflow.com/questions/37558821/how-to-replace-djangos-primary-key-with-a-different-integer-that-is-unique-for/37605582#37605582 — e4c5, Sep 12 '16 at 06:31
Ok maybe I need to get main reason answered. I'm building web app. The app will connect to a backend running mysql as the Db. The aim is to be a paid SAAS. Do I need to pay Oracle under the license or not? — Ka Tech, Sep 12 '16 at 10:15

Patrick · Accepted Answer · 2016-09-12T06:47:31.833

PostgreSQL has an extension called "uuid-ossp" with 4 algorithms that each implement one of the official UUID algorithms, the 4th one of which is random in 122 bits (the remaining 6 bits identify it as a version 4 UUID). All of these return a 16-byte UUID value.

(Note that a 64-bit value isn't a UUID at all, by definition. The MySQL reference you provide basically explains it already: only guaranteed to be unique if you do not break a set of rules. Perhaps they should call it a "LUID" because it definitely is not universally unique. This is the kind of tweak (take a defined standard and give it your own twist) that tastes like the evil of Microsoft. But I digress.)

In PostgreSQL:

CREATE EXTENSION "uuid-ossp";

With the extension installed you can use the uuid data type:

CREATE TABLE my_table (
    id    uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    ...
END;

Then you can insert a row in the table and retrieve the primary key in one go:

INSERT INTO my_table (...) VALUES (...)
RETURNING id;

The above statement will return the uuid value as a 16-byte binary value, not sure how you would manage that in PHP.

You can also just generate a uuid value without inserting a row:

SELECT uuid_generate_v4();

Brilliant exactly what I was after – Ka Tech Sep 12 '16 at 10:19 — Ka Tech, Sep 12 '16 at 10:19

UUID SHORT() for PostGres

1 Answers1