PostgreSQL 2202G Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2202G: invalid tablesample repeat

PostgreSQL error 2202G: invalid tablesample repeat occurs when an invalid seed value is passed to the REPEATABLE clause of a TABLESAMPLE query. The REPEATABLE option allows you to reproduce the same random sample by specifying a numeric seed, but PostgreSQL will reject values that are NULL, out of range, or of an incorrect data type. Understanding the root causes will help you fix and prevent this error quickly in production environments.

Top 3 Causes

1. Passing NULL to REPEATABLE

The most common cause is a NULL value reaching the REPEATABLE clause, typically from a dynamic query or application variable binding.

-- This will trigger error 2202G
SELECT * FROM orders TABLESAMPLE BERNOULLI(10) REPEATABLE(NULL);

-- Fix: Use COALESCE to guarantee a non-null seed
SELECT *
FROM orders
TABLESAMPLE BERNOULLI(10)
REPEATABLE(COALESCE(NULL, 42));

-- Fix: Use epoch timestamp as a safe fallback seed
SELECT *
FROM orders
TABLESAMPLE SYSTEM(5)
REPEATABLE(FLOOR(EXTRACT(EPOCH FROM NOW()))::INT);

2. Seed Value Out of Acceptable Range

Extremely large integers, negative values, or values outside the internally allowed range for a given sampling method can trigger this error.

-- Potentially problematic with very large seed values
-- SELECT * FROM orders TABLESAMPLE BERNOULLI(10) REPEATABLE(99999999999999);

-- Fix: Use MOD to cap the seed within a safe range
SELECT *
FROM orders
TABLESAMPLE BERNOULLI(10)
REPEATABLE(MOD(ABS(987654321), 1000000));

-- Fix: Create a reusable seed normalization function
CREATE OR REPLACE FUNCTION safe_seed(p_seed BIGINT)
RETURNS INT AS $$
BEGIN
    RETURN COALESCE(MOD(ABS(p_seed), 2147483647), 1)::INT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT *
FROM large_table
TABLESAMPLE SYSTEM(1)
REPEATABLE(safe_seed(9876543210));

3. Wrong Data Type Passed as Seed

Passing a non-numeric type such as a string or date without explicit casting will cause this error, especially in dynamically constructed queries.

-- Wrong: passing a plain string
-- SELECT * FROM orders TABLESAMPLE BERNOULLI(10) REPEATABLE('abc');

-- Fix: Explicit cast to integer
SELECT *
FROM orders
TABLESAMPLE BERNOULLI(10)
REPEATABLE('12345'::INT);

-- Fix: Convert a date to a numeric seed safely
SELECT *
FROM orders
TABLESAMPLE SYSTEM(5)
REPEATABLE(TO_CHAR(CURRENT_DATE, 'YYYYMMDD')::INT);

-- Fix: Hash-based seed from a string identifier
SELECT *
FROM customer_data
TABLESAMPLE BERNOULLI(20)
REPEATABLE(ABS(HASHTEXT('experiment-2024-q1')) % 100000);

Quick Fix Solutions

Always validate seed values before they reach the REPEATABLE clause.
Wrap TABLESAMPLE queries in a helper function that normalizes the seed input.
Use COALESCE to substitute a sensible default whenever a NULL seed is possible.

-- Safe wrapper function for reuse across your codebase
CREATE OR REPLACE FUNCTION safe_tablesample(
    p_percent NUMERIC,
    p_seed    BIGINT DEFAULT 42
)
RETURNS TABLE(id BIGINT) AS $$
DECLARE
    v_seed INT := COALESCE(MOD(ABS(p_seed), 2147483647), 1)::INT;
BEGIN
    RETURN QUERY
    SELECT t.id
    FROM my_table t
    TABLESAMPLE BERNOULLI(p_percent)
    REPEATABLE(v_seed);
END;
$$ LANGUAGE plpgsql;

Prevention Tips

Encapsulate seed normalization in a shared utility function. Never pass raw external input directly into REPEATABLE. Route all seed values through a validated function like safe_seed() shown above, and enforce this as a team coding standard via code reviews.
Add boundary-value tests to your CI/CD pipeline. Write automated tests covering NULL, 0, negative numbers, and maximum integer values for any query or function that uses TABLESAMPLE REPEATABLE. Using a framework like pgTAP makes it easy to catch this error before it ever reaches production.

Related Errors

2202H: invalid tablesample argument — Raised when the sampling percentage (not the seed) is invalid; e.g., outside the 0–100 range.
22003: numeric_value_out_of_range — May accompany 2202G when the seed value overflows PostgreSQL's numeric processing limits.
42601: syntax_error — Triggered by malformed TABLESAMPLE or REPEATABLE syntax, distinguishable by the error message detail.

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

DEV Community