UUID & Special Types in PostgreSQL

-- gen_random_uuid() is built-in; citext needs an extension
CREATE EXTENSION IF NOT EXISTS citext;

-- Create table with UUID
CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email CITEXT UNIQUE,              -- Case-insensitive text
  ip_address INET,                  -- IP address
  mac_address MACADDR,              -- MAC address
  price MONEY,                       -- Currency
  location POINT                     -- Geometric point
);

-- Insert with special types
INSERT INTO users VALUES
  (gen_random_uuid(), 
   'John@Example.com',              -- CITEXT matches any case
   '192.168.1.1',
   '08:00:27:1e:4f:3a',
   '$19.99',
   point(40.7128, -74.0060));       -- NYC coordinates

-- DETAILED_TAB:
-- Range types
CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER,
  during TSTZRANGE                  -- Timestamp range
);

-- Insert range
INSERT INTO bookings (room_id, during) VALUES
  (101, '[2024-01-01 14:00, 2024-01-01 16:00)');

-- Check overlap
SELECT * FROM bookings
WHERE during && '[2024-01-01 15:00, 2024-01-01 17:00)';