Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with data as (
select abs(('x'||translate(gen_random_uuid()::text,'-',''))::bit(64)::bigint % 5) n
from generate_series(1,100000,1) as x(i)
)
SELECT
    COUNT(*) cnt_n
     , n
from data
group by n;


cnt_n
n


20036
3

20230
4

19962
0

19933
2

19839
1



SELECT 5


-- deconstruct, reconstruct simplified
with decon as (
	select
		input_uuid
		, ('x'||substring(translate(input_uuid::text,'-',''),1,16))::bit(64)::bigint high
		, ('x'||substring(translate(input_uuid::text,'-',''),17,16))::bit(64)::bigint low
	from (
		select  gen_random_uuid() input_uuid
	) as x
)
, recon as (
	select
		*
		, (to_hex(high)||to_hex(low))::uuid reconstructed_uuid
	from decon
)
select * from recon


input_uuid
high
low
reconstructed_uuid


eb091c8f-147c-49d5-badf-546f56566f17
-1510644799156500011
-4981169825681871081
eb091c8f-147c-49d5-badf-546f56566f17



SELECT 1


--breakdown and reconstruction, step by step
with input as (
  select gen_random_uuid() input_uuid
  --select '4410ff0e-e392-4cc0-b5c0-44d332794a5a'::uuid input_uuid
)
, cleaned as (
  select input_uuid
  , replace(input_uuid::text,'-','') cleaned_uuid
  from input
)
, split as (
  select
    input_uuid
  , cleaned_uuid
  , 'x'||substring(cleaned_uuid,1,16) high_hex
  , 'x'||substring(cleaned_uuid,17,16) low_hex
  from cleaned
)
, binstring as (
  select
    input_uuid
  , cleaned_uuid
  , high_hex
  , low_hex
  , high_hex::bit(64) high_binstring
  , low_hex::bit(64) low_binstring
  from split
)
--select * from binstring;
, nums as (
  select
    input_uuid
  , cleaned_uuid
  , high_hex
  , low_hex
  , high_binstring
  , low_binstring
  , high_binstring::bigint high_bigint
  , low_binstring::bigint low_bigint
  from binstring
)

, reconstruct_hex as (
	select
			input_uuid
	  , cleaned_uuid
	  , high_hex
	  , low_hex
	  , high_binstring
	  , low_binstring
	  , high_bigint
	  , low_bigint
	  , to_hex(high_bigint) reconstructed_high_hex
	  , to_hex(low_bigint) reconstructed_low_hex
	  , to_hex(high_bigint) || to_hex(low_bigint) reconstructed
	  , (to_hex(high_bigint) || to_hex(low_bigint))::uuid reconstructed_uuid
  from nums
)
select
	  *
	, reconstructed_uuid = input_uuid
from reconstruct_hex;


input_uuid
cleaned_uuid
high_hex
low_hex
high_binstring
low_binstring
high_bigint
low_bigint
reconstructed_high_hex
reconstructed_low_hex
reconstructed
reconstructed_uuid
?column?


34ac7aeb-5f83-4c7a-8d5f-6761afb92be6
34ac7aeb5f834c7a8d5f6761afb92be6
x34ac7aeb5f834c7a
x8d5f6761afb92be6
0011010010101100011110101110101101011111100000110100110001111010
1000110101011111011001110110000110101111101110010010101111100110
3795543737304829050
-8259769522316563482
34ac7aeb5f834c7a
8d5f6761afb92be6
34ac7aeb5f834c7a8d5f6761afb92be6
34ac7aeb-5f83-4c7a-8d5f-6761afb92be6
t



SELECT 1