README.md

Postgresql tools, info, sql, ...

Postgresql uuidv7

UuidV7 and other uuid-based ones include, for example, the fact that the prefix is a timestamp, which makes indexing enjoyable and the timestamp does not need to be separately stored. It can be used for sorting just like SERIAL type keys.

• IETF
• Why uuidv7 is better ...
• Good bye integers, welcome uuids
• Kvelakur C-language version using SSL Rand -uuidv4
• My ksh shellscript to generate uuidV7
• My uuid tools collection

Extension

• PG_uuidv7 extension

Functions

• PG uuidv7 functions

My select from previous functions

Generate uuidv7

CREATE OR REPLACE FUNCTION public.uuid_generate_v7()
RETURNS uuid
AS $$
  -- use random v4 uuid as starting point (which has the same variant we need)
  -- then overlay timestamp
  -- then set version 7 by flipping the 2 and 1 bit in the version 4 string
select encode(
    set_bit(
      set_bit(
        overlay(uuid_send(gen_random_uuid())
                placing substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
                from 1 for 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex')::uuid;
$$
language SQL
volatile;

Get timestamp from uuidv7

CREATE OR REPLACE FUNCTION public.timestamp_from_uuid_v7(_uuid uuid)
RETURNS timestamp without time zone
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT LEAKPROOF
AS $$
  SELECT to_timestamp(('x0000' || substr(_uuid::text, 1, 8) || substr(_uuid::text, 10, 4))::bit(64)::bigint::numeric / 1000);
$$
language SQL
volatile;
;