انواع داده پیشرفته PostgreSQL

PostgreSQL یکی از غنی‌ترین مجموعه انواع داده را دارد – از JSONB و ARRAY تا UUID، INET، tsvector و حتی Range Types. در این فصل با انواع داده پیشرفته آشنا می‌شویم که PostgreSQL را از سایر دیتابیس‌ها متمایز می‌کنند.

یادآوری انواع پایه

SERIAL vs IDENTITY

-- روش قدیمی (هنوز کار می‌کند)
CREATE TABLE products_old (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- روش جدید و توصیه‌شده (SQL standard)
CREATE TABLE products (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

-- تفاوت: BY DEFAULT یعنی می‌توان مقدار دستی هم داد
CREATE TABLE products_v2 (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO products_v2 (id, name) VALUES (1000, 'دستی');  -- OK
INSERT INTO products_v2 (name) VALUES ('خودکار');         -- OK

UUID – شناسه یکتای جهانی

-- نصب extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- جدول با UUID
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO users (username) VALUES ('ali');
SELECT * FROM users;
--                  id                  | username | created_at
-- --------------------------------------+----------+---------------
--  a1b2c3d4-e5f6-...-...-12345          | ali      | 2026-...

-- یا با gen_random_uuid (داخل PG 13+، نیاز به extension ندارد)
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT
);

UUID vs SERIAL

آرایه‌ها (ARRAY)

PostgreSQL پشتیبانی native از array دارد:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],                    -- آرایه متن
    sizes INTEGER[],                -- آرایه عدد
    prices NUMERIC(10,2)[]          -- آرایه عددی
);

-- درج
INSERT INTO products (name, tags, sizes) VALUES
    ('فرش کاشان', ARRAY['پشمی', 'دستباف', 'کاشان'], ARRAY[3, 6, 9, 12]),
    ('گلیم', ARRAY['پنبه', 'سنتی'], ARRAY[2, 4, 6]),
    ('تابلو فرش', '{ابریشم,نقاشی,صنعتی}', '{1,2,3}');  -- syntax دیگر

-- خواندن
SELECT name, tags[1] AS first_tag, tags FROM products;
SELECT name, sizes[2:4] AS some_sizes FROM products;  -- slice

-- جستجو
SELECT * FROM products WHERE 'دستباف' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['پشمی'];   -- contains
SELECT * FROM products WHERE tags && ARRAY['ابریشم', 'پشمی'];  -- overlap

-- تعداد
SELECT name, array_length(tags, 1) AS tag_count FROM products;

-- unnest - تبدیل آرایه به ردیف
SELECT name, unnest(tags) AS tag FROM products;

-- aggregation
SELECT array_agg(name) FROM products;
SELECT array_agg(DISTINCT tag) 
FROM products, unnest(tags) AS tag;

توابع مهم آرایه

SELECT 
    array_length(ARRAY[1,2,3,4], 1)        AS length,        -- 4
    array_position(ARRAY['a','b','c'], 'b') AS position,     -- 2
    array_remove(ARRAY[1,2,3,2,1], 2)       AS removed,      -- {1,3,1}
    array_replace(ARRAY[1,2,3], 2, 99)      AS replaced,     -- {1,99,3}
    ARRAY[1,2,3] || ARRAY[4,5,6]            AS concat,       -- {1,2,3,4,5,6}
    string_to_array('a,b,c', ',')           AS to_array,     -- {a,b,c}
    array_to_string(ARRAY[1,2,3], '-')      AS to_string;    -- 1-2-3

JSON و JSONB

PostgreSQL دو نوع JSON دارد:

• JSON: متن خام JSON، حفظ ترتیب و whitespace
• JSONB: باینری بهینه‌شده، قابل ایندکس‌گذاری، سریع‌تر برای جستجو

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB,
    metadata JSONB
);

-- درج
INSERT INTO products (name, attributes) VALUES
    ('فرش کاشان', '{"color": "قرمز", "size": "3x4", "knot_count": 250000, "warranty_years": 10}'),
    ('گلیم', '{"color": "آبی", "size": "2x3", "weave_type": "kilim"}'),
    ('تابلو فرش', '{"color": "متعدد", "size": "1.5x2", "subject": "نقاشی"}');

-- دسترسی به فیلدها
SELECT name, attributes->'color' AS color FROM products;
-- attributes->'color' = JSON value
-- attributes->>'color' = TEXT value (بدون quotes)

SELECT name, attributes->>'color' AS color FROM products;

-- nested
SELECT attributes->'address'->>'city' FROM customers;
-- یا
SELECT attributes#>>'{address,city}' FROM customers;

-- جستجو
SELECT * FROM products WHERE attributes->>'color' = 'قرمز';
SELECT * FROM products WHERE (attributes->'knot_count')::int > 100000;

-- contains
SELECT * FROM products WHERE attributes @> '{"color": "قرمز"}';
SELECT * FROM products WHERE attributes @> '{"warranty_years": 10}';

-- key exists
SELECT * FROM products WHERE attributes ? 'warranty_years';
SELECT * FROM products WHERE attributes ?| ARRAY['size', 'weight'];  -- any
SELECT * FROM products WHERE attributes ?& ARRAY['color', 'size'];   -- all

تغییر JSONB

-- افزودن/بروزرسانی فیلد
UPDATE products
SET attributes = attributes || '{"discount": 0.1}'
WHERE id = 1;

-- یا
UPDATE products
SET attributes = jsonb_set(attributes, '{discount}', '0.15')
WHERE id = 1;

-- nested set
UPDATE products
SET attributes = jsonb_set(attributes, '{address,city}', '"تهران"')
WHERE id = 1;

-- حذف فیلد
UPDATE products
SET attributes = attributes - 'discount'
WHERE id = 1;

-- حذف چند فیلد
UPDATE products
SET attributes = attributes - ARRAY['discount', 'old_field'];

-- حذف nested
UPDATE products
SET attributes = attributes #- '{address,zip}'
WHERE id = 1;

توابع JSONB

-- استخراج کلیدها
SELECT jsonb_object_keys(attributes) FROM products WHERE id = 1;

-- اطلاعات هر کلید-مقدار
SELECT key, value
FROM products, jsonb_each(attributes)
WHERE id = 1;

-- JSONB از rows
SELECT row_to_json(p) FROM products p;
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'tags', tags
) FROM products;

-- آرایه از JSON
SELECT jsonb_array_elements('[1,2,3,4]'::jsonb);
-- 1
-- 2
-- 3
-- 4

-- pretty print
SELECT jsonb_pretty(attributes) FROM products WHERE id = 1;

ایندکس روی JSONB

-- GIN index برای @>، ?، ?|، ?&
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- ایندکس روی فیلد خاص (B-tree)
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- ایندکس روی expression
CREATE INDEX idx_products_knots ON products (((attributes->'knot_count')::int));

hstore – key-value ساده

CREATE EXTENSION hstore;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    attrs HSTORE
);

INSERT INTO products (attrs) VALUES
    ('color => قرمز, size => M, weight => 2.5'),
    ('"author" => "فردوسی", "year" => "1010"');

-- خواندن
SELECT attrs->'color' FROM products;

-- جستجو
SELECT * FROM products WHERE attrs ? 'color';
SELECT * FROM products WHERE attrs @> 'color => قرمز';

برای داده ساده hstore بهتر از JSONB است (کوچک‌تر و سریع‌تر)، اما برای داده nested از JSONB استفاده کنید.

Range Types – بازه‌ها

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    period TSTZRANGE,                  -- بازه timestamp
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)  -- جلوگیری از overlap
);

-- درج
INSERT INTO reservations (room_id, period) VALUES
    (1, '[2026-05-01, 2026-05-05)'),     -- [ inclusive، ) exclusive
    (1, '[2026-05-10, 2026-05-15)'),
    (2, '[2026-05-01, 2026-05-03)');

-- این خطا می‌دهد - overlap با اولی
INSERT INTO reservations (room_id, period) VALUES
    (1, '[2026-05-03, 2026-05-07)');     -- ERROR

-- جستجو
SELECT * FROM reservations 
WHERE period @> '2026-05-02 10:00'::timestamptz;     -- contains زمان

SELECT * FROM reservations 
WHERE period && '[2026-05-04, 2026-05-12)'::tstzrange;  -- overlap

-- توابع
SELECT 
    lower(period) AS start,
    upper(period) AS end,
    upper(period) - lower(period) AS duration
FROM reservations;

انواع Range

• INT4RANGE، INT8RANGE – عدد
• NUMRANGE – numeric
• TSRANGE، TSTZRANGE – timestamp
• DATERANGE – تاریخ

INET و CIDR – آدرس IP

CREATE TABLE access_log (
    id SERIAL PRIMARY KEY,
    user_ip INET,
    network CIDR,
    accessed_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO access_log (user_ip, network) VALUES
    ('192.168.1.10', '192.168.1.0/24'),
    ('10.0.0.5', '10.0.0.0/8'),
    ('::1', '::/128');

-- جستجو
SELECT * FROM access_log WHERE user_ip << '192.168.0.0/16';  -- contained in
SELECT * FROM access_log WHERE network >> '10.0.0.5';         -- contains

-- توابع
SELECT 
    user_ip,
    family(user_ip) AS family,           -- 4 یا 6
    host(user_ip) AS host,
    netmask(user_ip),
    network(user_ip)
FROM access_log;

ENUM – مقادیر محدود

-- تعریف ENUM
CREATE TYPE order_status AS ENUM (
    'pending', 'processing', 'shipped', 'delivered', 'cancelled'
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO orders (status) VALUES ('pending'), ('shipped');
-- INSERT INTO orders (status) VALUES ('invalid');  -- ERROR

SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status < 'shipped';  -- ENUM مرتب است

-- اضافه کردن مقدار جدید
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';

TSVECTOR – برای Full-Text Search

(در فصل ۹ مفصل بحث می‌کنیم)

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR
);

UPDATE articles
SET search_vector = to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(content, ''));

CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- جستجو
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('simple', 'فرش & کاشان');

Custom Types – انواع سفارشی

-- Composite type
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    postal_code TEXT,
    country TEXT
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    home_address ADDRESS,
    work_address ADDRESS
);

INSERT INTO customers (name, home_address) VALUES
    ('علی', ROW('خ. ولیعصر', 'تهران', '12345', 'ایران')::address);

SELECT name, (home_address).city FROM customers;
SELECT name, home_address.city FROM customers;  -- syntax دیگر

DOMAIN – constraints قابل استفاده مجدد

-- domain با constraint
CREATE DOMAIN phone_iran AS TEXT
    CHECK (VALUE ~ '^09d{9}$');

CREATE DOMAIN positive_money AS NUMERIC(12,2)
    CHECK (VALUE > 0);

CREATE DOMAIN email_address AS TEXT
    CHECK (VALUE ~ '^[^@]+@[^@]+.[^@]+$');

-- استفاده
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email EMAIL_ADDRESS,
    phone PHONE_IRAN,
    balance POSITIVE_MONEY DEFAULT 0
);

INSERT INTO users (email, phone, balance) 
VALUES ('ali@example.com', '09121234567', 1000);

-- این error می‌دهد
INSERT INTO users (email, phone) VALUES ('invalid-email', '123');

Generated Columns

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC(10,2),
    quantity INTEGER,
    total_value NUMERIC(12,2) GENERATED ALWAYS AS (price * quantity) STORED
);

INSERT INTO products (name, price, quantity) VALUES ('فرش', 5000000, 10);

SELECT * FROM products;
-- id | name | price   | quantity | total_value
--  1 | فرش  | 5000000 |       10 |    50000000

بهترین شیوه‌ها

• TEXT به‌جای VARCHAR(n)
• TIMESTAMPTZ به‌جای TIMESTAMP (timezone-aware)
• NUMERIC برای پول (نه FLOAT)
• JSONB به‌جای JSON
• UUID برای microservices، BIGINT IDENTITY برای monolith
• ARRAY فقط برای داده‌های ذاتاً آرایه‌ای – برای روابط، جدول جدا بسازید
• ENUM برای وضعیت‌های ثابت – یا lookup table اگر متغیرند
• DOMAIN برای validation قابل استفاده مجدد

جمع‌بندی

• PostgreSQL غنی‌ترین انواع داده را دارد
• JSONB برای ذخیره داده schema-less – با ایندکس GIN قدرتمند
• ARRAY native، با عملگرهای @>، &&
• UUID برای شناسه توزیع‌شده
• Range Types برای بازه‌ها با constraint جلوگیری از overlap
• INET/CIDR برای IP
• ENUM، DOMAIN و Custom Types برای type safety