How to fix Data exception in PostgreSQL

Examine PostgreSQL logs and your application error output to identify which query caused the error. Run the query directly in psql with SET client_min_messages = NOTICE; to get more detailed feedback:

SET client_min_messages = NOTICE;
SELECT ...;  -- Your failing query

The error message should include additional context about which column or operation failed.

Use the \d meta-command in psql to view the table structure:

\d table_name;

Check the data types, size limits (VARCHAR length), NOT NULL constraints, and any CHECK constraints. For example:
- VARCHAR(20) means the column accepts only up to 20 characters
- NUMERIC(5,2) means 5 total digits with 2 after the decimal point
- NOT NULL means the column cannot accept NULL values

Before running INSERT or UPDATE statements, verify the data in your application code:

// JavaScript example
const firstName = "John Doe Extension";
if (firstName.length > 20) {
  console.error("First name exceeds 20 character limit");
  firstName = firstName.substring(0, 20); // Truncate explicitly
}

Always validate:
- String length against column VARCHAR limits
- Numeric ranges (check if value fits in the defined precision)
- Required vs optional fields (NULL handling)
- Date formats match PostgreSQL expectations (YYYY-MM-DD)

If the error occurs during type casting, explicitly convert values before attempting the cast:

-- BAD: Direct cast of potentially invalid string
SELECT CAST(user_input AS INTEGER);

-- GOOD: Validate the string format first or use error handling
SELECT CAST(TRIM(user_input) AS INTEGER);

-- In PL/pgSQL, use exception handling
BEGIN
  SELECT CAST(user_input AS INTEGER) INTO numeric_var;
EXCEPTION WHEN invalid_text_representation THEN
  RAISE NOTICE 'Invalid numeric format: %', user_input;
END;

Use NULLIF() to handle edge cases like division by zero:

SELECT amount / NULLIF(quantity, 0) AS price_per_unit;

If the issue is that your column is too small or has wrong constraints, modify the schema:

-- Increase string column size
ALTER TABLE users ALTER COLUMN first_name TYPE VARCHAR(50);

-- Change numeric precision
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(10,2);

-- Add/remove NOT NULL constraint
ALTER TABLE orders ALTER COLUMN customer_id DROP NOT NULL;
ALTER TABLE orders ALTER COLUMN total_amount SET NOT NULL;

Be cautious with these changes in production—they may require data migration if tightening constraints.