PostgreSQL 2202H Error: Causes and Solutions Complete Guide

PostgreSQL Error 2202H: invalid tablesample argument

The 2202H: invalid tablesample argument error occurs when an invalid value is passed as the argument to PostgreSQL's TABLESAMPLE clause. The TABLESAMPLE feature allows you to retrieve a random subset of rows from a table using either BERNOULLI or SYSTEM sampling methods, both of which require a numeric percentage between 0 and 100. Passing values outside this range, NULL, or a non-numeric expression triggers this error immediately.

---

Top 3 Causes

1. Percentage Value Out of Range (0–100)

The most common cause is simply passing a value less than 0 or greater than 100.

-- ❌ Causes error: value exceeds 100
SELECT * FROM orders TABLESAMPLE BERNOULLI(150);
-- ERROR:  invalid tablesample argument
-- DETAIL:  Argument must be between 0 and 100.

-- ❌ Causes error: negative value
SELECT * FROM orders TABLESAMPLE SYSTEM(-5);

-- ✅ Correct usage
SELECT * FROM orders TABLESAMPLE BERNOULLI(10);
SELECT * FROM orders TABLESAMPLE SYSTEM(1);

2. NULL Value Passed as Argument

When dynamically building queries in PL/pgSQL or application code, uninitialized variables or null results can be passed directly into the TABLESAMPLE clause.

-- ❌ NULL variable causes error
DO $$
DECLARE
    pct NUMERIC; -- uninitialized = NULL
BEGIN
    EXECUTE 'SELECT * FROM orders TABLESAMPLE BERNOULLI(' || pct || ')';
END;
$$;

-- ✅ Use COALESCE to provide a safe default
DO $$
DECLARE
    pct      NUMERIC := NULL;
    safe_pct NUMERIC;
BEGIN
    safe_pct := COALESCE(pct, 10); -- default to 10%
    EXECUTE 'SELECT COUNT(*) FROM orders TABLESAMPLE BERNOULLI($1)'
    USING safe_pct;
    RAISE NOTICE 'Sampled with %% percentage', safe_pct;
END;
$$;

3. Wrong Data Type or Malformed Expression

Passing a string literal or an expression that cannot be implicitly cast to a numeric type will also trigger this error, especially in dynamically constructed SQL.

-- ❌ String with non-numeric content causes error
DO $$
DECLARE
    pct TEXT := '10 percent';
BEGIN
    EXECUTE 'SELECT * FROM orders TABLESAMPLE BERNOULLI(' || pct || ')';
END;
$$;

-- ✅ Explicit cast to NUMERIC with validation
DO $$
DECLARE
    pct      TEXT    := '10';
    safe_pct NUMERIC;
BEGIN
    safe_pct := pct::NUMERIC;

    IF safe_pct < 0 OR safe_pct > 100 THEN
        RAISE EXCEPTION 'Percentage out of range: %', safe_pct;
    END IF;

    EXECUTE 'SELECT COUNT(*) FROM orders TABLESAMPLE BERNOULLI($1)'
    USING safe_pct;
END;
$$;

---

Quick Fix Solutions

Use the following safe wrapper pattern for all dynamic TABLESAMPLE queries:

-- Safe reusable sampling function
CREATE OR REPLACE FUNCTION safe_sample(
    p_table TEXT,
    p_pct   NUMERIC DEFAULT 10,
    p_method TEXT DEFAULT 'BERNOULLI'
)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    safe_pct NUMERIC;
BEGIN
    -- Clamp value between 0.001 and 100, handle NULL
    safe_pct := GREATEST(0.001, LEAST(100, COALESCE(p_pct, 10)));

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

    EXECUTE format(
        'SELECT COUNT(*) FROM %I TABLESAMPLE %s(%s)',
        p_table, p_method, safe_pct
    );
    RAISE NOTICE 'Sampled % using %s at %%%', p_table, p_method, safe_pct;
END;
$$;

-- Usage
SELECT safe_sample('orders', 5, 'BERNOULLI');
SELECT safe_sample('orders', NULL);   -- defaults to 10%
SELECT safe_sample('orders', 150);    -- clamped to 100%

---

Prevention Tips

Validate and clamp inputs before querying. Always apply GREATEST(0, LEAST(100, value)) and COALESCE(value, default) when the sampling percentage comes from user input, configuration, or external systems. Use parameterized queries with the USING clause instead of string concatenation to avoid both type errors and SQL injection.

Standardize a team-wide wrapper function. Create a shared utility function (like safe_sample above) and write unit tests covering boundary values (0, 100, -1, 101, NULL). Integrate these tests into your CI/CD pipeline to catch regressions before they reach production.

---

Related Errors

• 2202G: invalid tablesample repeat — Triggered when the REPEATABLE(seed) value is invalid within a TABLESAMPLE clause.
• 22003: numeric_value_out_of_range — May appear alongside this error when numeric overflow occurs during argument computation.
• 42601: syntax_error — Common companion error when dynamically building malformed TABLESAMPLE SQL strings.

---

📖 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.