PostgresDB 22003: Numeric value out of range

What is PostgresDB 22003: Numeric value out of range

When encountering the error 22003: Numeric value out of range from a PostgreSQL database, perform the following actions immediately:

Identify the Query Causing the Error: Check the application logs to find the specific query that caused the error. This will help in pinpointing the exact column and value causing the issue.Inspect the Data Type and Size: For the column identified, check its data type and size constraints. Use the following SQL command to get the schema, table name, column name, and data type for all columns in your database:

SELECT table_schema, table_name, column_name, data_type FROM information_schema.columnsWHERE table_schema NOT IN ('information_schema', 'pg_catalog');

Check the Inserted/Updated Value: Verify the value that your application is trying to insert or update. Ensure it is within the range allowed for the data type of the column identified in step 2. For numeric types, PostgreSQL documentation provides the ranges for each numeric type.Adjust the Query or Data: If the value is indeed out of the acceptable range, you will need to either adjust the value to fit within the range or alter the column to accommodate larger (or smaller) values. For altering the column, use the following SQL command, replacing table_name, column_name, and new_data_type with the appropriate values:

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

Check System and Table Statistics: If the error persists or for deeper investigation, check PostgreSQL's system and table statistics for anomalies. You might want to check for anything unusual in table sizes, row counts, or system resources usage that could indicate a broader issue.

To check table sizes:

SELECT relname AS "Table", pg_size_pretty(pg_total_relation_size(C.oid)) AS "Size"FROM pg_class CLEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)WHERE nspname NOT IN ('pg_catalog', 'information_schema')AND C.relkind <> 'i'AND nspname !~ '^pg_toast'ORDER BY pg_total_relation_size(C.oid) DESCLIMIT 10;

To check row counts for suspect tables:

SELECT relname AS "Table", n_live_tup AS "RowCount"FROM pg_stat_user_tablesORDER BY n_live_tup DESC;

These steps should help in identifying and addressing the immediate cause of the "22003: Numeric value out of range" error in PostgreSQL.