PostgreSQL Deep Dive Part 2 - Character Types, Binary Data, and UUIDs

CREATE TABLE text_comparison (
    fixed_char CHAR(10),
    var_char VARCHAR(10),
    unlimited_text TEXT
);
 
-- Let's see how they handle different input lengths
INSERT INTO text_comparison VALUES
    ('short', 'short', 'short'),
    ('exactly_ten', 'exactly_ten', 'exactly_ten'),
    ('this_is_longer_than_ten', 'this_is_longer_than_ten', 'this_is_longer_than_ten'); -- Error: as char and varchar won't allow higher size
 
INSERT INTO text_comparison VALUES
    ('short', 'short', 'short'),
    ('exactlyTen', 'exactlyTen', 'exactly_ten'),
    ('exactlyTen','exactlyTen','this_is_longer_than_ten');
 
--| fixed_char  | var_char   | unlimited_text          |
--|-------------|------------|-------------------------|
--| 'short    ' | short      | short                   |
--| exactlyTen  | exactlyTen | exactly_ten             |
--| exactlyTen  | exactlyTen | this_is_longer_than_ten |

SELECT concat('|', fixed_char, '|')
FROM text_comparison
WHERE fixed_char = 'short';
-- Output: |short     |  -- Notice the padding spaces

SELECT pg_column_size(var_char) as storage_size, var_char
FROM text_comparison;
-- Reveals variable storage size based on content length
 
--| storage_size | var_char   |
--|--------------|------------|
--|            6 | short      |
--|           11 | exactlyTen |
--|           11 | exactlyTen |

\l -- database list does have column to show encoding.
 
show client_encoding; -- UTF8
show server_encoding; -- UTF8
 
-- Creating a case-insensitive collation
CREATE COLLATION case_insensitive (
    provider = icu, -- this stands for International Components for Unicode
    locale = 'en-US-u-ks-level1',
    deterministic = false
);
 
-- Practical application
CREATE TABLE user_tags (
    tag_name TEXT COLLATE case_insensitive UNIQUE
);
--This creates a table user_tags with a single column tag_name that:
--Is case-insensitive for comparisons and sorting
--Must contain unique values (case-insensitive)
 
INSERT INTO user_tags VALUES ('Programming');
INSERT INTO user_tags VALUES ('programming'); -- This will fail due to case-insensitive uniqueness:

CREATE TABLE binary_storage (
    file_name TEXT,
    file_data BYTEA,
    file_hash BYTEA
);
 
-- Storing different types of binary data
INSERT INTO binary_storage (file_name, file_data, file_hash) VALUES
    ('example.txt',
    decode('48656C6C6F20576F726C64', 'hex'),
    sha256('Hello World'));
--| file_name   |       file_data    |      file_hash     |
--|-------------|--------------------|--------------------|
--| example.txt | BINARY - 11 bytes  | BINARY - 32 bytes  |

-- Compare different hash storage methods and sizes
select pg_typeof(md5('hello world')); -- text
select pg_typeof(sha256('hello world')); -- bytea
 
-- to convert md5 to bytea
select pg_typeof(decode(md5('hello world'), 'hex')); -- bytea
 
SELECT
    pg_column_size(digest('test data', 'md5')) as md5_bytea_size, -- bytea
    pg_column_size(md5('test data')) as md5_text_size, -- md5
    pg_column_size(md5('test data')::uuid) as md5_uuid_size; -- uuid
 
--| md5_bytea_size | md5_text_size | md5_uuid_size |
--|----------------|---------------|---------------|
--|             20 |            36 |            16 |

CREATE TABLE distributed_entities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    data JSONB
);
 
INSERT INTO
	distributed_entities (data)
VALUES
	('{"a": "b"}');
 
--| id                       | created_at           | data       |
--|--------------------------|----------------------|------------|
--| b1b8c4d6-304c-4ca6-98... | 2024-12-24 10:17:... | {"a": "b"} |
 
-- Different UUID generation methods
SELECT
    gen_random_uuid() as random_uuid,
    uuid_generate_v1() as timestamp_based_uuid,
    uuid_generate_v4() as random_uuid_v4;
 
-- uuid_generate_v1 and uuid_generate_v4 will need the below extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Compare storage sizes
SELECT
    pg_column_size('12345678-1234-5678-1234-567812345678'::uuid) as uuid_size,
    pg_column_size('12345678-1234-5678-1234-567812345678') as text_size;
 
--| uuid_size | text_size |
--|-----------|-----------|
--|        16 |        37 |
 
-- UUID only takes 16 bytes
-- If we convert UUID to Text, then it changes to ~40 bytes.
-- If you are storing UUID, store in UUID type.