Data Types (Advanced)

CREATE DOMAIN EMAIL AS TEXT;

-- Usage
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email EMAIL NOT NULL UNIQUE
);

CREATE DOMAIN TEL AS TEXT;

-- Usage
CREATE TABLE contacts (
  id UUID PRIMARY KEY,
  phone TEL
);

CREATE DOMAIN RATING AS REAL CHECK (VALUE >= 0 AND VALUE <= 5);

-- Usage
CREATE TABLE reviews (
  id UUID PRIMARY KEY,
  product_rating RATING DEFAULT 0
);

CREATE DOMAIN PERCENTAGE AS REAL;

-- Usage
CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  completion PERCENTAGE DEFAULT 0
);

CREATE DOMAIN URL AS TEXT;

-- Usage
CREATE TABLE projects (
  id UUID PRIMARY KEY,
  website URL
);

CREATE DOMAIN DURATION AS BIGINT;

-- Usage
CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  duration DURATION
);

CREATE DOMAIN COLOR AS VARCHAR(16);

-- Usage
CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  color COLOR
);

CREATE DOMAIN AVATAR AS TEXT;

-- Usage
CREATE TABLE profiles (
  id UUID PRIMARY KEY,
  avatar AVATAR
);

CREATE DOMAIN RICH_TEXT AS TEXT;

-- Usage
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  content RICH_TEXT
);

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  title TEXT NOT NULL,
  cover FILE,
  attachments FILE,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Single image
COMMENT ON COLUMN tasks.cover IS '{"accept":"image/*"}';

-- Multiple files
COMMENT ON COLUMN tasks.attachments IS '{"accept":"*"}';

-- With size limit
COMMENT ON COLUMN tasks.document IS '{"accept":".pdf","maxSize":10485760,"maxFiles":1}';

// Images only
{"accept": "image/*"}

// Specific image types
{"accept": "image/png,image/jpeg"}

// Documents
{"accept": ".pdf,.doc,.docx"}

// Any file
{"accept": "*"}

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  name TEXT NOT NULL,
  description RICH_TEXT,
  price DECIMAL(10,2) NOT NULL,

  -- Contact
  website URL,
  support_email EMAIL,
  phone TEL,

  -- Metrics
  rating RATING DEFAULT 0,
  completion PERCENTAGE,

  -- Media
  cover FILE,
  logo AVATAR,

  -- Customization
  brand_color COLOR,

  -- Metadata
  processing_time DURATION,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Configure file columns
COMMENT ON COLUMN products.cover IS '{"accept":"image/*","maxSize":2097152}';
COMMENT ON COLUMN products.logo IS '{"accept":"image/*","maxSize":1048576}';