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


20117
3

20132
4

19961
0

19881
2

19909
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


cbe70b9f-007c-42da-add5-b3b4cad9c001
-3754018986854169894
-5920628546029895679
cbe70b9f-007c-42da-add5-b3b4cad9c001



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?


d835184b-ce31-44a1-b59b-e721f721ba5e
d835184bce3144a1b59be721f721ba5e
xd835184bce3144a1
xb59be721f721ba5e
1101100000110101000110000100101111001110001100010100010010100001
1011010110011011111001110010000111110111001000011011101001011110
-2867358873890503519
-5360436798388323746
d835184bce3144a1
b59be721f721ba5e
d835184bce3144a1b59be721f721ba5e
d835184b-ce31-44a1-b59b-e721f721ba5e
t



SELECT 1