عدد، متن و boolean: numeric برای پول، text در برابر varchar

نوع داده‌ی درست، باگ‌های آینده را حذف می‌کند

انواع عددی

حد integer برای مبالغ ریالی خطرناک است: 2,147,483,647 ریال فقط حدود ۲۱۴ میلیون تومان است و یک فاکتور فرش ماشینی عمده به‌راحتی از آن رد می‌شود. برای مبلغ ریالی بدون اعشار bigint، و اگر اعشار (دلار، یورو، نرخ) دارید numeric(18,2) یا دقت بیشتر.

SELECT 0.1::float8 + 0.2::float8 = 0.3;        -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3;      -- true
SELECT 7 / 2, 7 / 2.0, 7::numeric / 2;         -- 3 | 3.5000000000000000 | 3.5000000000000000
SELECT round(2.5::numeric), round(2.5::float8); -- 3 | 2

نوع money را کنار بگذارید: نمایشش به تنظیم lc_monetary سرور وابسته است و با تغییر آن، همان داده متفاوت خوانده می‌شود.

text، varchar و char

در پستگرس text و varchar(n) دقیقاً یک ساختار ذخیره‌سازی دارند و از نظر سرعت فرقی نمی‌کنند؛ n فقط یک قید طول است. char(n) با فاصله پر می‌شود و تقریباً هیچ‌وقت انتخاب خوبی نیست. توصیه‌ی رایج: text به‌علاوه‌ی CHECK برای قاعده‌ی کسب‌وکار.

CREATE TABLE designs (
  code   text PRIMARY KEY CHECK (code ~ '^[A-Z]{2}-[0-9]{3,5}$'),
  title  text NOT NULL CHECK (length(title) BETWEEN 2 AND 120),
  colors smallint NOT NULL CHECK (colors BETWEEN 1 AND 16),
  is_active boolean NOT NULL DEFAULT true
);
SELECT length('فرش کاشان'), octet_length('فرش کاشان');   -- 9 | 17

مقادیر بلندتر از حدود ۲ کیلوبایت به‌طور خودکار فشرده و در جدول جانبی TOAST ذخیره می‌شوند؛ پس ذخیره‌ی توضیحات طولانی در text هیچ جریمه‌ای برای بقیه‌ی ستون‌ها ندارد.

boolean

boolean سه حالت دارد: true، false و NULL. ورودی‌های 't'، 'yes'، 'on' و '1' همه true پذیرفته می‌شوند. در WHERE مستقیم بنویسید WHERE is_active یا WHERE NOT is_active.

نکته‌هایی که کمتر کسی می‌داند

• round روی numeric نیمه را از صفر دور می‌کند (2.5 به 3) اما روی float8 به نزدیک‌ترین زوج (2.5 به 2)؛ اختلاف چندریالی فاکتورها گاهی از همین است.
• تبدیل varchar(50) به varchar(100) یا به text جدول را بازنویسی نمی‌کند و فوری است؛ اما کوتاه کردن طول یا تغییر به نوعی دیگر، کل جدول را با قفل انحصاری بازنویسی می‌کند.
• هر کاراکتر فارسی در UTF-8 دو بایت است؛ octet_length برای برآورد حجم و length برای شمارش حروف. نیم‌فاصله هم یک کاراکتر (سه بایت) حساب می‌شود.
• از نسخه‌ی 14 numeric مقدار Infinity هم می‌پذیرد؛ اگر نمی‌خواهید، CHECK (price < 'Infinity') یا یک سقف واقعی بگذارید.
• تبدیل رشته‌ی دارای ارقام فارسی به عدد خطا می‌دهد: '۱۲۳'::int نامعتبر است. در ورود داده translate(s, '۰۱۲۳۴۵۶۷۸۹', '0123456789') را بزنید.