Last Updated: February 25, 2016

Postgresql Unique K-ordered 64-bit Integer Generator

Postgresql function - http://www.wekeroad.com/2014/05/29/a-better-id-generator-for-postgresql/.

Gave this function an additional spin by removing separate schema creation, so it could be used in the public schema:

CREATE SEQUENCE global_id_sequence;

CREATE OR REPLACE FUNCTION id_generator(OUT result bigint) AS $$
DECLARE
 our_epoch bigint := 1314220021721;
 seq_id bigint;
 now_millis bigint;
 -- the id of this DB shard, must be set for each
 -- schema shard you have - you could pass this as a parameter too
 shard_id int := 1;
BEGIN
 SELECT nextval('global_id_sequence') % 1024 INTO seq_id;

 SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
 result := (now_millis - our_epoch) << 23;
 result := result | (shard_id << 10);
 result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

Usage:

CREATE TABLE test_table
(
 id bigint NOT NULL DEFAULT id_generator(),
 name text NOT NULL,
 CONSTRAINT test_table_pkey PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);