🗄 10 قابلیت کمترشناخته‌شده SQL که هر Developer باید بداند(پارت 4️⃣)

🔄 6. ءUPSERT یا INSERT ... ON CONFLICT

اگر Row جدید است، آن را Insert کن؛ اگر از قبل وجود دارد، آن را Update کن.
این یک نیاز رایج است که معمولاً به یک SELECT، یک IF و دو مسیر کدنویسی جداگانه نیاز دارد.
ءUPSERT تمام این کارها را در یک Statement اتمیک انجام می‌دهد و بین Check و Write نیز Race Condition ایجاد نمی‌شود.

ALTER TABLE shipments.shipments
ADD CONSTRAINT shipments_number_unique
UNIQUE (number);

INSERT INTO shipments.shipments (
    id,
    number,
    order_id,
    address_street,
    address_city,
    address_zip,
    carrier,
    receiver_email,
    status,
    created_at,
    updated_at
)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'SH-2024-001',
    'ORD-2024-001',
    '123 Main St',
    'New York',
    '10001',
    'FedEx',
    'customer@example.com',
    'pending',
    NOW(),
    NOW()
)
ON CONFLICT (number) DO UPDATE SET
    carrier = EXCLUDED.carrier,
    status = EXCLUDED.status,
    updated_at = GREATEST(
        shipments.updated_at,
        EXCLUDED.updated_at
    );

ابتدا یک Unique Constraint روی Column مربوط به number اضافه می‌کنیم؛ یعنی همان Columnای که Conflict بر اساس آن شناسایی می‌شود.
سپس INSERT ... ON CONFLICT (number) DO UPDATE تلاش می‌کند Row را Insert کند. اگر Rowای با همان number از قبل وجود داشته باشد، به‌جای Insert، آن را Update می‌کند.
ءPseudo-table مربوط به EXCLUDED مقادیری را نگه می‌دارد که تلاش کرده‌اید Insert کنید.
بنابراین:

carrier = EXCLUDED.carrier

یعنی:
«از مقدار Carrier جدید استفاده کن.»
عبارت زیر:

GREATEST(
    shipments.updated_at,
    EXCLUDED.updated_at
)

مقدار جدیدتر از بین دو Timestamp را نگه می‌دارد.
یک Statement، بدون Duplicate Row و بدون Race Condition از نوع Read-Modify-Write بین Callerهای هم‌زمان.

نکته: این Syntax مربوط به PostgreSQL است. در SQL استاندارد، مانند SQL Server و Oracle، از Statement مربوط به MERGE استفاده می‌شود. در MySQL نیز از عبارت زیر استفاده می‌شود:
INSERT ... ON DUPLICATE KEY UPDATE

🧩7. پشتیبانی از JSON

همه‌ی داده‌ها رابطه‌ای نیستند. گاهی لازم است یک payload منعطف و نیمه‌ساخت‌یافته ذخیره کنید؛ مثلاً یک event، بدنه‌ی یک webhook یا یک blob مربوط به تنظیمات.
ءPostgreSQL داده‌های JSON را به‌صورت native در نوع JSONB ذخیره می‌کند و اجازه می‌دهد داخل آن query بزنید؛ بنابراین برای JSONهای موردی، نیازی به یک document database جداگانه ندارید. همچنین مجبور نیستید JSON را به‌صورت string ذخیره کنید و پردازش بیشتر آن را به کد backend بسپارید؛ روشی که تمام قابلیت‌های index را از دست می‌دهد.

CREATE TABLE shipments.events (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

-- Insert sample data
INSERT INTO shipments.events (payload)
VALUES
    ('{"type":"click","coordinates":[{"x":10,"y":20},{"x":15,"y":25}]}'),
    ('{"type":"hover","coordinates":[{"x":5,"y":30}]}'),
    ('{"type":"scroll","coordinates":[{"x":0,"y":100},{"x":0,"y":200},{"x":0,"y":300}]}');

-- Extract simple JSON fields
SELECT
    payload ->> 'type' AS event_type,
    payload -> 'coordinates' -> 0 ->> 'x' AS first_x,
    payload -> 'coordinates' -> 0 ->> 'y' AS first_y
FROM shipments.events;

جدول events یک payload از نوع JSONB ذخیره می‌کند. سپس query وارد آن می‌شود:
->> یک مقدار را به‌صورت text استخراج می‌کند.
-> یک JSON object یا یک عنصر تو‌در‌تو از یک array را استخراج می‌کند.
بنابراین عبارت زیر، مقدار x از اولین coordinate را می‌خواند:

payload -> 'coordinates' -> 0 ->> 'x'

ءJSONB به‌صورت binary و parse‌شده ذخیره می‌شود و می‌توان روی آن index ساخت؛ بنابراین می‌توانید بدون scan کردن کل documentها، روی آن‌ها filter و extract انجام دهید.

📝 نکته: SQL Server برای query زدن روی JSON از JSON_VALUE و OPENJSON استفاده می‌کند؛ استاندارد SQL نیز JSON_TABLE را اضافه می‌کند؛ قابلیتی که در Oracle، MySQL و PostgreSQL 17+ وجود دارد و یک JSON array را مستقیماً به ردیف‌های relational تبدیل می‌کند.