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;
    }

Answer 1

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();