پستگرس

Webhook (دریافت سفارش)
  → PostgreSQL (Execute Query)
    INSERT INTO orders (customer_id, details, metadata)
    VALUES (
      {{$json.customer_id}},
      '{{JSON.stringify($json.items)}}'::jsonb,
      '{"source": "website", "ip": "{{$json.ip}}"}'::jsonb
    )
    RETURNING id, created_at

-- جستجو در JSON
SELECT * FROM orders
WHERE details->>'status' = 'paid'
AND (metadata->>'amount')::int > 100000

-- جستجوی آرایه در JSON
SELECT * FROM products
WHERE tags ? 'electronics'

-- جستجوی تودرتو
SELECT * FROM users
WHERE profile->'address'->>'city' = 'Tehran'

Webhook (جستجو)
  → PostgreSQL (Execute Query)
    SELECT id, title, description,
      ts_rank(search_vector, query) as rank
    FROM articles,
      to_tsquery('simple', '{{$json.keyword}}') query
    WHERE search_vector @@ query
    ORDER BY rank DESC
    LIMIT 20

هر رویداد → PostgreSQL (Insert)
  - Table: activity_logs
  - Columns: user_id, action, details (JSONB), ip_address, created_at

-- بعداً گزارش:
SELECT action, COUNT(*) as count,
  DATE(created_at) as date
FROM activity_logs
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY action, DATE(created_at)
ORDER BY date DESC, count DESC

INSERT INTO daily_stats (date, page, views)
VALUES (CURRENT_DATE, '{{$json.page}}', 1)
ON CONFLICT (date, page)
DO UPDATE SET views = daily_stats.views + 1
RETURNING *

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', created_at) as month,
    SUM(amount) as total,
    COUNT(*) as orders
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '12 months'
  GROUP BY month
),
growth AS (
  SELECT *,
    LAG(total) OVER (ORDER BY month) as prev_month,
    ROUND((total - LAG(total) OVER (ORDER BY month))
      / LAG(total) OVER (ORDER BY month) * 100, 1) as growth_pct
  FROM monthly_sales
)
SELECT * FROM growth ORDER BY month DESC

SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
  salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
  SUM(salary) OVER (ORDER BY salary DESC) as running_total
FROM employees

-- ایجاد تریگر
CREATE OR REPLACE FUNCTION notify_new_order()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('new_order', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_created
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order();

COPY (
  SELECT id, name, email, phone
  FROM customers
  WHERE active = true
) TO '/tmp/customers.csv'
WITH CSV HEADER

Schedule (هر 5 دقیقه) → PostgreSQL
  SELECT
    (SELECT COUNT(*) FROM users WHERE active) as total_users,
    (SELECT COUNT(*) FROM orders WHERE DATE(created_at) = CURRENT_DATE) as today_orders,
    (SELECT COALESCE(SUM(amount),0) FROM orders WHERE DATE(created_at) = CURRENT_DATE) as today_revenue,
    (SELECT COUNT(*) FROM tickets WHERE status = 'open') as open_tickets
  → Set (فرمت فارسی) → Telegram (ارسال)

INSERT INTO users (name, email)
   VALUES ('Ali', 'ali@test.com')
   RETURNING id, created_at;

-- دسترسی به فیلد (text)
   data->>'name'

   -- دسترسی تودرتو (json)
   data->'address'->'city'

   -- بررسی وجود کلید
   data ? 'email'

   -- بروزرسانی JSON
   data || '{"status": "active"}'::jsonb

   -- حذف کلید
   data - 'temp_field'

-- آرایه
   SELECT * FROM products WHERE 'tag1' = ANY(tags);

   -- همپوشانی آرایه
   SELECT * FROM products WHERE tags && ARRAY['tag1', 'tag2'];

-- فاصله زمانی
   WHERE created_at >= NOW() - INTERVAL '30 days'

   -- گروه‌بندی بر اساس ماه
   DATE_TRUNC('month', created_at)

   -- استخراج
   EXTRACT(YEAR FROM created_at)

-- مقدار پیش‌فرض
   COALESCE(phone, 'ندارد')

   -- جلوگیری از تقسیم بر صفر
   amount / NULLIF(quantity, 0)

SELECT c.name, latest_order.*
   FROM customers c
   CROSS JOIN LATERAL (
     SELECT * FROM orders
     WHERE customer_id = c.id
     ORDER BY created_at DESC
     LIMIT 1
   ) latest_order

CREATE MATERIALIZED VIEW monthly_report AS
   SELECT ... FROM ... GROUP BY ...;

   -- بروزرسانی
   REFRESH MATERIALIZED VIEW monthly_report;

-- B-tree (پیش‌فرض)
   CREATE INDEX idx_email ON users(email);

   -- GIN (برای JSONB و آرایه)
   CREATE INDEX idx_data ON orders USING GIN (data);

   -- GiST (برای Full-text Search)
   CREATE INDEX idx_search ON articles USING GiST (search_vector);

   -- Partial Index
   CREATE INDEX idx_active ON users(email) WHERE active = true;

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'new';

-- تولید تاریخ‌های 30 روز اخیر
    SELECT generate_series(
      CURRENT_DATE - INTERVAL '30 days',
      CURRENT_DATE,
      '1 day'
    )::date as date

-- ترکیب با جداکننده
    string_agg(name, ', ')

    -- فرمت عدد
    to_char(amount, '999,999,999')

    -- Regex
    SELECT * FROM users WHERE email ~ '^[a-z]+@gmail\.com$'

SELECT name,
      to_char(price / 10, 'FM999,999,999') || ' تومان' as price_display
    FROM products

WITH RECURSIVE category_tree AS (
      SELECT id, name, parent_id, 0 as depth
      FROM categories WHERE parent_id IS NULL
      UNION ALL
      SELECT c.id, c.name, c.parent_id, ct.depth + 1
      FROM categories c
      JOIN category_tree ct ON c.parent_id = ct.id
    )
    SELECT * FROM category_tree ORDER BY depth, name

// فیلد خاص
    {{$json.id}}

    // تعداد رکوردها
    {{$json.length}}

    // RETURNING fields
    {{$json.id}} // ID رکورد جدید

ALTER USER n8n_user WITH PASSWORD 'new_password';

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO n8n_user;

SELECT table_name FROM information_schema.tables
  WHERE table_schema = 'public';

ALTER TABLE my_table ALTER COLUMN description TYPE TEXT;