DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

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 the seed value provided to the REPEATABLE clause of a TABLESAMPLE query is invalid. The TABLESAMPLE feature allows you to extract random row samples from a table, and the REPEATABLE(seed) option makes sampling deterministic by fixing the random seed. This error is triggered when the seed value is NULL, NaN, Infinity, or otherwise outside the acceptable range.

Top 3 Causes

1. Passing NaN or NULL as the seed value

The most common cause is directly passing NaN, NULL, or Infinity to the REPEATABLE clause, either hardcoded or through a miscalculated expression.

-- These will all trigger error 2202G:
SELECT * FROM orders TABLESAMPLE BERNOULLI(10) REPEATABLE(NULL);
SELECT * FROM orders TABLESAMPLE BERNOULLI(10) REPEATABLE('NaN'::float8);
SELECT * FROM orders TABLESAMPLE BERNOULLI(10) REPEATABLE('Infinity'::float8);

-- Correct usage with a valid finite number:
SELECT * FROM orders TABLESAMPLE BERNOULLI(10) REPEATABLE(42);
SELECT * FROM orders TABLESAMPLE SYSTEM(5) REPEATABLE(12345);
Enter fullscreen mode Exit fullscreen mode

2. Dynamic seed generation from application code

When building queries dynamically in application code (Python, Java, etc.), the seed parameter can accidentally become None, NaN, or an invalid float before being bound to the SQL query.

-- Problematic pattern: unvalidated seed passed dynamically
-- (Pseudocode: cursor.execute("SELECT * FROM t TABLESAMPLE BERNOULLI(10) REPEATABLE(%s)", (seed,)))
-- If seed is None or float('nan'), this produces error 2202G.

-- Safe pattern: validate seed before use with a wrapper function
CREATE OR REPLACE FUNCTION sample_table(p_pct FLOAT, p_seed FLOAT)
RETURNS SETOF orders
LANGUAGE plpgsql AS $$
BEGIN
    -- Replace invalid seed with a safe default
    IF p_seed IS NULL OR p_seed != p_seed THEN  -- NaN check: NaN != NaN
        p_seed := 1.0;
    END IF;
    RETURN QUERY
        SELECT * FROM orders TABLESAMPLE BERNOULLI(p_pct) REPEATABLE(p_seed);
END;
$$;

-- Usage
SELECT * FROM sample_table(10.0, NULL);   -- safely defaults to seed 1.0
SELECT * FROM sample_table(10.0, 99.0);   -- uses seed 99.0
Enter fullscreen mode Exit fullscreen mode

3. Using computed expressions that may yield invalid floats

Seed values are sometimes derived from expressions like EXTRACT(EPOCH FROM ...) or arithmetic operations. If the computation produces an overflow or NaN, the error is triggered.

-- Risky: division-based seed that could produce NaN or invalid result
SELECT * FROM orders
TABLESAMPLE BERNOULLI(10)
REPEATABLE(some_col / another_col)  -- division by zero -> NaN -> 2202G
FROM params;

-- Safe: use COALESCE + explicit cast to guard against invalid values
SELECT * FROM orders
TABLESAMPLE BERNOULLI(10)
REPEATABLE(
    COALESCE(
        NULLIF(EXTRACT(EPOCH FROM NOW())::bigint % 100000, 0),
        42
    )
);

-- Always test your seed expression independently first:
SELECT EXTRACT(EPOCH FROM NOW())::bigint % 100000 AS seed_value;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Replace invalid seeds immediately — Use COALESCE to fall back to a safe default: 
-- Safe seed with fallback
SELECT * FROM orders
TABLESAMPLE BERNOULLI(10)
REPEATABLE(COALESCE(:seed_param, 42));
Enter fullscreen mode Exit fullscreen mode
  1. Validate seed values in a CHECK constraint when storing sampling configs: 
CREATE TABLE sampling_jobs (
    id SERIAL PRIMARY KEY,
    pct FLOAT CHECK (pct > 0 AND pct <= 100),
    seed FLOAT CHECK (
        seed IS NOT NULL AND
        seed = seed AND          -- blocks NaN
        seed < 'Infinity'::float -- blocks Infinity
    )
);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always wrap TABLESAMPLE usage in a validated helper function. Centralizing seed validation in a single PL/pgSQL function prevents invalid values from ever reaching the REPEATABLE clause and makes your sampling logic consistent across the codebase.

  • Add boundary-value tests to your CI pipeline. Test your sampling queries with edge-case inputs (NULL, NaN, 0, negative numbers, Infinity) before deploying to production. A simple regression test that verifies your wrapper function handles these gracefully will eliminate the majority of 2202G occurrences in production environments.

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

Top comments (0)