📋

Description

Error 22000, 'data exception', indicates that data being processed by PostgreSQL violates a fundamental data integrity rule or expectation. This typically occurs when an operation attempts to store, retrieve, or manipulate data that is incompatible with the column's data type, format, or defined constraints.

💬

Error Message

data exception

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

Attempting to insert or update a value into a column with an incompatible data type, such as putting text into an integer column.

⚠️

Invalid Character Encoding

Inserting characters that are not valid for the column's specified character encoding, leading to data corruption or rejection.

⚠️

Numeric Value Out of Range

Providing a numeric value that exceeds the defined precision or scale of a numeric or decimal column, causing an overflow.

⚠️

Invalid Date/Time Format

Supplying a date or time string that does not conform to the expected format for a date, time, or timestamp column.

🛠️

Solutions

3 solutions available

1. Identify and Correct Invalid Data in Specific Columns medium

Pinpoints columns with problematic data and provides methods to clean them.

1

Examine your application's recent data insertion or update statements. The 'data exception' error often arises from attempting to insert data that doesn't conform to the column's data type or constraints.

2

If possible, review the specific values being inserted for the relevant table and columns. Look for common issues like incorrect date formats, non-numeric values in numeric columns, or values exceeding defined limits.

3

Use `ALTER TABLE` to temporarily relax constraints or change data types if you suspect a widespread data issue that needs to be addressed in bulk. **Caution:** This can lead to data integrity issues if not done carefully.

ALTER TABLE your_table ALTER COLUMN your_column TYPE VARCHAR(255) USING your_column::VARCHAR;
-- After cleaning, you can change it back
-- ALTER TABLE your_table ALTER COLUMN your_column TYPE INT USING your_column::INT;

4

If you have a large dataset, you might need to write a script to identify and clean the problematic rows. For example, to find rows in a 'numeric_column' that cannot be cast to an integer:

SELECT * FROM your_table WHERE your_numeric_column::TEXT !~ '^\d+$';
-- Or for dates:
SELECT * FROM your_table WHERE your_date_column::TEXT !~ '^\d{4}-\d{2}-\d{2}$';

5

Once identified, update or delete the offending rows. For instance, to update invalid numeric entries to NULL:

UPDATE your_table SET your_numeric_column = NULL WHERE your_numeric_column::TEXT !~ '^\d+$';

2. Validate Input Data Before Insertion/Update medium

Implements checks in your application code to prevent invalid data from reaching the database.

1

Review your application's data handling logic. Ensure that all incoming data is validated against the expected data types, formats, and ranges before it's sent to PostgreSQL.

2

For numeric types, check if the input is a valid number (integer, float, etc.).

if (!isNumeric(input_value)) { throw new Error('Invalid numeric input'); }

3

For date/time types, validate the format (e.g., YYYY-MM-DD HH:MI:SS).

if (!isValidDateFormat(input_value, 'YYYY-MM-DD')) { throw new Error('Invalid date format'); }

4

For text fields, check for length constraints and disallowed characters if applicable.

if (input_value.length > MAX_LENGTH) { throw new Error('Input too long'); }

5

Consider using database functions or stored procedures for more complex validation logic that can be reused across your application.

CREATE OR REPLACE FUNCTION validate_email(email_address TEXT) RETURNS BOOLEAN AS $$
BEGIN
  RETURN email_address ~ '^[^@]+@[^@]+\.[^@]+$';
END;
$$ LANGUAGE plpgsql;

3. Check for Data Type Mismatches During Data Loading medium

Addresses errors occurring during bulk data imports from external sources.

1

If you are using `COPY` command or other bulk loading tools, meticulously check the format of your source data file against the target table's schema.

2

Pay close attention to column delimiters, text qualifiers (e.g., double quotes), and null value representations in your source file.

Example `COPY` command:
COPY your_table FROM '/path/to/your/data.csv' DELIMITER ',' CSV HEADER;

3

Ensure that the order of columns in your source file matches the order specified in the `COPY` command or implicitly expected by PostgreSQL.

If your CSV has columns A, B, C and your table has columns C, A, B, you need to specify the column order:
COPY your_table (column_c, column_a, column_b) FROM '/path/to/your/data.csv' DELIMITER ',' CSV HEADER;

4

If you encounter the error during a `COPY` operation, try loading the data into a staging table with a more permissive data type (e.g., `TEXT` for all columns) first. Then, use SQL statements to transform and insert the data into your final table, allowing for more granular error handling.

CREATE TABLE staging_table (col1 TEXT, col2 TEXT);
COPY staging_table FROM '/path/to/your/data.csv' DELIMITER ',' CSV HEADER;

INSERT INTO your_table (numeric_column, date_column)
SELECT col1::INT, col2::DATE FROM staging_table WHERE col1 ~ '^\d+$' AND col2 ~ '^\d{4}-\d{2}-\d{2}$';
-- Handle errors or invalid rows separately.

🔗

Related Errors

5 related errors

PostgreSQL Error 22000: Data Exception Encountered