PostgreSQL 2202H Error: Causes and Solutions Complete Guide

PostgreSQL Error 2202H: Invalid TABLESAMPLE Argument

PostgreSQL error 2202H (invalid_tablesample_argument) occurs when you pass an out-of-range or otherwise invalid argument to a TABLESAMPLE method. The two built-in methods, BERNOULLI and SYSTEM, both require a percentage value strictly between 0 and 100 (inclusive boundaries vary by implementation). This error frequently catches developers off-guard when building dynamic sampling queries or migrating code from other databases.

---

Top 3 Causes

1. Sampling percentage is out of the valid range (0–100)

The most common cause is passing a value greater than 100 or less than or equal to 0. Developers sometimes confuse the percentage format (10 = 10%) with a decimal fraction (0.1 = 10% in some other tools).

-- ERROR: argument must be between 0 and 100
SELECT * FROM orders TABLESAMPLE BERNOULLI(150);

-- ERROR: 0 is not a valid sampling percentage
SELECT * FROM orders TABLESAMPLE SYSTEM(0);

-- CORRECT: sample 10% of rows using BERNOULLI
SELECT * FROM orders TABLESAMPLE BERNOULLI(10);

-- CORRECT: sample ~5% using block-level SYSTEM method
SELECT * FROM orders TABLESAMPLE SYSTEM(5);

---

2. Negative or NULL value passed dynamically

When building dynamic queries in application code or PL/pgSQL, unvalidated user input can inject negative numbers or NULL as the sampling argument.

-- ERROR: negative value causes 2202H
DO $$
DECLARE
    rate NUMERIC := -5;
BEGIN
    EXECUTE format(
        'SELECT * FROM orders TABLESAMPLE BERNOULLI(%s)', rate
    );
END;
$$;

-- SAFE: validate before executing
DO $$
DECLARE
    rate NUMERIC := -5;
BEGIN
    IF rate IS NULL OR rate <= 0 OR rate > 100 THEN
        RAISE EXCEPTION 'Invalid sample rate: %. Must be between 0 and 100.', rate;
    END IF;

    EXECUTE format(
        'SELECT * FROM orders TABLESAMPLE BERNOULLI(%s)', rate
    );
END;
$$;

---

3. Wrong argument type for custom TABLESAMPLE methods

Extensions like tsm_system_rows accept a row count (integer) instead of a percentage. Passing 0, a negative number, or a percentage-style float causes 2202H.

-- Install the extension first
CREATE EXTENSION IF NOT EXISTS tsm_system_rows;

-- ERROR: 0 or negative row count is invalid
SELECT * FROM orders TABLESAMPLE system_rows(0);
SELECT * FROM orders TABLESAMPLE system_rows(-500);

-- CORRECT: specify a positive integer row count
SELECT * FROM orders TABLESAMPLE system_rows(1000);

-- Quick comparison of all three methods
SELECT 'BERNOULLI' AS method, COUNT(*) FROM orders TABLESAMPLE BERNOULLI(10)
UNION ALL
SELECT 'SYSTEM',    COUNT(*) FROM orders TABLESAMPLE SYSTEM(10)
UNION ALL
SELECT 'system_rows', COUNT(*) FROM orders TABLESAMPLE system_rows(1000);

---

Quick Fix Solutions

Use a wrapper function to centralize validation and eliminate repeated boilerplate:

CREATE OR REPLACE FUNCTION safe_tablesample(
    p_table  TEXT,
    p_rate   NUMERIC,
    p_method TEXT DEFAULT 'BERNOULLI'
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_rate IS NULL OR p_rate <= 0 OR p_rate > 100 THEN
        RAISE EXCEPTION
            '[2202H] Invalid tablesample argument: %. Rate must be > 0 and <= 100.',
            p_rate;
    END IF;

    IF upper(p_method) NOT IN ('BERNOULLI', 'SYSTEM') THEN
        RAISE EXCEPTION 'Unsupported TABLESAMPLE method: %', p_method;
    END IF;

    EXECUTE format(
        'SELECT COUNT(*) FROM %I TABLESAMPLE %s(%s)',
        p_table, p_method, p_rate
    );
END;
$$;

-- Usage
SELECT safe_tablesample('orders', 10, 'BERNOULLI');  -- OK
SELECT safe_tablesample('orders', -1, 'BERNOULLI');  -- Raises clear error

---

Prevention Tips

Clamp dynamic values before use — instead of rejecting bad input outright, consider clamping it to a safe range using GREATEST/LEAST when the business logic allows approximation:

-- Clamp any input to a safe 0.1–100 range
SELECT *
FROM orders
TABLESAMPLE BERNOULLI(
    GREATEST(0.1, LEAST(100, :user_provided_rate))
);

Add boundary tests to your CI pipeline — always test TABLESAMPLE queries with edge-case values: 0, 100, 101, -1, and NULL. Catching these in automated tests prevents production incidents and makes intent explicit to future maintainers.

---

Related Errors

Code	Name	Description
2202G	invalid_tablesample_repeat	Invalid seed value in the REPEATABLE clause
22023	invalid_parameter_value	General invalid parameter, seen in custom TABLESAMPLE methods
42601	syntax_error	Malformed TABLESAMPLE syntax before argument validation even occurs

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.