PostgreSQL 22005 (error_in_assignment) appears when a value cannot be coerced into the target column or variable type during INSERT, UPDATE, COPY, or assignment.
PostgreSQL Error 22005 (error_in_assignment) means the database could not convert a supplied value to the target data type. Check the offending column, correct the literal’s format or cast it explicitly, then rerun the statement.
PostgreSQL Error 22005
PostgreSQL throws condition name error_in_assignment (SQLSTATE 22005) when it cannot cast or coerce an incoming value to the data type of a column, variable, or function argument. The engine stops the statement and rolls back the transaction.
The failure shows up during INSERT, UPDATE, COPY, SELECT INTO, or PL/pgSQL assignments.
Fixing it fast matters because the entire data change is rejected, leaving the table unchanged and the application possibly stalled.
The error is triggered by unparseable literals, out-of-range numbers, incorrect date or time formats, mismatched encodings, or disabled extensions that previously supplied casts.
Implicit type conversion hides the problem until runtime.
Bulk loads and ETL jobs see it when CSV values contain stray characters or when locale settings differ between client and server, making implicit conversions fail.
First reproduce the error with a single-row INSERT to isolate the failing column.
Then correct the value’s format or apply an explicit CAST (value::target_type) so PostgreSQL understands the conversion path.
If the incoming data is valid but too large, alter the column type (e.g., smallint to integer) or widen numeric precision. When loading external data, use COPY ... FROM with a FORMAT clause and an appropriate NULL or DELIMITER option.
Numeric overflow: Inserting 80000 into SMALLINT raises 22005.
Solution: change the column to INTEGER or cast the value to int2 if it fits.
Date mismatch: Loading '31/12/2024' with default ISO locale fails. Fix by using to_date('31/12/2024','DD/MM/YYYY') or setting DateStyle to DMY.
Text to boolean: Value 'yes' into BOOLEAN fails. Convert with CAST('yes' AS BOOLEAN) only if TRUE/FALSE mapping is configured, otherwise map values in application code.
Validate and sanitize data in the application or staging table before final insert.
Always specify column lists in INSERT statements to avoid accidental ordering mistakes.
Use explicit casts in queries, enforce CHECK constraints for range limits, and log ETL jobs with pg_logical or custom error tables so bad rows are quarantined rather than killing the whole load.
22P02 invalid_text_representation: Similar failure when casting text to numeric or boolean. Fix by trimming or casting explicitly.
22007 invalid_datetime_format: Date literal not matching DateStyle. Use to_date or set lc_time and DateStyle properly.
.
Yes. Unless you are in a savepoint or using ON_ERROR_STOP off in psql, PostgreSQL aborts the whole transaction when 22005 occurs.
Re-run the statement with a LIMIT 1 filter per column or wrap it in PL/pgSQL with EXCEPTION handling to log the problematic value.
Often yes. Setting DateStyle to match the incoming literal format allows PostgreSQL to parse the value without explicit to_date calls.
Galaxy’s SQL editor highlights type mismatches in real time and its AI copilot suggests correct casts, reducing the chance of 22005 reaching production.
.avif)
.avif){kind=logo}
