PostgreSQL error 2202H occurs when a TABLESAMPLE clause receives an invalid argument. The sampling percentage must be between 0 and 100, and arguments must be numeric constants that can be evaluated at query planning time.
Error 2202H ("invalid_tablesample_argument") is raised when PostgreSQL encounters an invalid argument passed to the TABLESAMPLE clause in a SELECT query. The TABLESAMPLE clause allows you to retrieve a random sample of rows from a table using either BERNOULLI or SYSTEM sampling methods. Both methods require a numeric argument representing the sampling percentage. PostgreSQL validates TABLESAMPLE arguments at query planning time and enforces strict requirements: the argument must be a numeric literal or a stable expression that evaluates to a number between 0 and 100 inclusive. Any violation of these constraints triggers error 2202H, preventing the query from executing.
The most common cause of error 2202H is providing a percentage outside the valid range. Ensure your TABLESAMPLE argument is a numeric value between 0 and 100 inclusive.
-- Incorrect: percentage is negative
SELECT * FROM my_table TABLESAMPLE BERNOULLI(-5);
-- Incorrect: percentage exceeds 100
SELECT * FROM my_table TABLESAMPLE SYSTEM(150);
-- Correct: valid percentage between 0-100
SELECT * FROM my_table TABLESAMPLE BERNOULLI(10);
SELECT * FROM my_table TABLESAMPLE SYSTEM(25);TABLESAMPLE requires a numeric constant or stable expression that can be evaluated at query planning time. You cannot use variables, parameters, or non-deterministic functions.
-- Incorrect: using a variable (will fail)
SELECT * FROM my_table TABLESAMPLE BERNOULLI(@sampling_percent);
-- Incorrect: using a subquery result
SELECT * FROM my_table TABLESAMPLE BERNOULLI((SELECT percentage FROM config));
-- Incorrect: using a non-deterministic function
SELECT * FROM my_table TABLESAMPLE BERNOULLI(random() * 100);
-- Correct: numeric literal
SELECT * FROM my_table TABLESAMPLE BERNOULLI(10);
-- Correct: stable expression with constants
SELECT * FROM my_table TABLESAMPLE BERNOULLI(5 * 2);Verify that the TABLESAMPLE clause follows the correct syntax with proper parentheses and method name.
-- Correct syntax for BERNOULLI
SELECT * FROM table_name TABLESAMPLE BERNOULLI(percentage);
-- Correct syntax for SYSTEM
SELECT * FROM table_name TABLESAMPLE SYSTEM(percentage);
-- With optional REPEATABLE clause for consistent samples
SELECT * FROM table_name TABLESAMPLE BERNOULLI(10) REPEATABLE(42);If you need to get the same sample multiple times (useful for testing or reporting), add the REPEATABLE clause with a seed value:
-- Get the same 10% sample every time with seed 42
SELECT * FROM my_table TABLESAMPLE BERNOULLI(10) REPEATABLE(42);
-- Another query with the same seed produces the same sample
SELECT COUNT(*) FROM my_table TABLESAMPLE BERNOULLI(10) REPEATABLE(42);The seed value can be any non-negative integer.
If you need dynamic sampling percentages determined at runtime, you cannot use them directly in TABLESAMPLE. Instead, use application logic to calculate percentages or use PostgreSQL cursors:
-- Approach 1: Use application logic to determine and embed the value
-- (Determine percentage_value in your application, then use it in SQL)
PREPARE sample_query (numeric) AS
SELECT * FROM my_table TABLESAMPLE BERNOULLI($1);
EXECUTE sample_query(10);
-- Approach 2: Calculate total rows and use LIMIT with ORDER BY random
SELECT * FROM my_table
ORDER BY random()
LIMIT (SELECT count(*) * 0.1 FROM my_table)::int;TABLESAMPLE Method Selection: PostgreSQL provides two sampling methods, each with different trade-offs. BERNOULLI selects each row independently with the specified probability, providing more uniform randomness but requiring more computation. SYSTEM performs block-level sampling, selecting entire disk blocks if they pass the probability threshold, which is faster on large tables but may produce slightly less uniform distributions. For most use cases, BERNOULLI (10) is a good default choice. On very large tables where performance matters more than perfect randomness, try SYSTEM. Note that TABLESAMPLE cannot be used on views, temporary tables, or foreign tables in most PostgreSQL configurations. If you encounter "relation does not support sampling" errors, you will need to sample from the underlying base tables instead. Error 2202H specifically validates the argument itself; other constraint violations produce different error codes.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL