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


19975
3

19813
4

20115
0

20216
2

19881
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


abf07bc1-84c1-4ac4-9cea-25d40ebffc48
-6057205427727152444
-7139852666531742648
abf07bc1-84c1-4ac4-9cea-25d40ebffc48



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;

ERROR:  invalid input syntax for type uuid: "2cccd15eea7479aab63509efd69030a"