How to fix Invalid text representation in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 323 views

This error occurs when PostgreSQL cannot convert a text value to the expected data type (integer, boolean, date, UUID, etc.). It means the string contains invalid characters or format for the target type.

Quick fix

Fixes 22P02

\d your_table_name
  1. Identify the problematic query and data type
  2. Validate and clean your input data
  3. Use explicit CAST syntax with safe conversion
  4. Use TRY_CAST or conditional casting to handle failures gracefully
postgresql — error
\d your_table_name
Invalid text representation

What this error means

PostgreSQL is strict about data type conversions. When you attempt to cast or insert a string value into a column expecting a specific type, PostgreSQL validates the input. If the string contains characters or formatting that doesn't match the target type, the database rejects the operation with a "Invalid text representation" error (SQLSTATE 22P02). Unlike MySQL or SQLite, PostgreSQL enforces type safety at the database level, making this error essential to catch data quality issues early. Common scenarios include passing string IDs instead of integers, empty strings to numeric columns, invalid dates, or non-hexadecimal strings to UUID columns.

Typical symptoms

  • Query fails with "ERROR: invalid input syntax for type {datatype}"
  • INSERT or UPDATE operations fail when loading entities with non-matching ID types
  • SELECT queries return 500 errors when parameter types don't match column types
  • Bulk COPY operations fail midway through data import
  • Application receives PDOException or database driver errors mentioning "invalid text representation"

Common causes

  1. Casting a non-numeric string (e.g., "abc") to an integer or numeric type
  2. Passing an empty string to an integer or numeric column
  3. Attempting to cast an invalid date format (e.g., "2023-02-30") to DATE type
  4. Inserting a malformed UUID (not matching 8-4-4-4-12 hexadecimal pattern) into a UUID column
  5. Passing non-boolean values ("yes", "no", "1", "0" as text) to a BOOLEAN column
  6. Using URL parameters directly in queries without validation (e.g., `/posts/{id}` where id should be numeric)
  7. Locale differences between client and server affecting number or date parsing

How to fix "Invalid text representation"

1Identify the problematic query and data type

Review the error message to find the column name and expected data type. Use psql to check your schema:

bash
\d your_table_name

This shows all columns and their data types. Look for the column mentioned in the error message.

2Validate and clean your input data

Before inserting or casting, ensure the string matches the expected format:

sql
-- Check for non-numeric characters in a column you're casting to integer
SELECT id, some_value FROM your_table WHERE some_value ~ '[^0-9]';

-- Check for empty strings that should be integers
SELECT id FROM your_table WHERE some_int_field = '';

If you find bad data, either clean it or handle it conditionally.

3Use explicit CAST syntax with safe conversion

Replace implicit casts with explicit ones using the :: operator or CAST() function:

sql
-- Safe integer cast
SELECT id::INTEGER FROM my_table;

-- Or using CAST syntax
SELECT CAST(id AS INTEGER) FROM my_table;

-- For dates, specify the format
SELECT TO_DATE(date_string, 'YYYY-MM-DD') FROM my_table;

-- For numeric values with localization
SELECT TO_NUMBER(amount, '999D99') FROM my_table;
4Use TRY_CAST or conditional casting to handle failures gracefully

Instead of failing on invalid input, catch and handle conversion errors:

sql
-- PostgreSQL doesn't have TRY_CAST natively, but you can use CASE
SELECT 
  CASE 
    WHEN some_value ~ '[^0-9]' THEN NULL
    ELSE some_value::INTEGER
  END as cleaned_value
FROM your_table;

-- Or use pg_catalog functions for safer conversion
SELECT 
  COALESCE(NULLIF(some_value, '')::INTEGER, 0) as result
FROM your_table;
5Fix type mismatches at the application level

If the error occurs in your application code (ORM, driver), validate inputs before passing to queries:

typescript
// In a Node.js/TypeScript application
const userId = parseInt(userIdString, 10);
if (isNaN(userId)) {
  throw new Error('Invalid user ID format');
}

const result = await db.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);

// With Prisma
const userId = parseInt(idParam);
if (isNaN(userId)) throw new Error('Invalid ID');

const user = await prisma.user.findUnique({
  where: { id: userId }
});
6Verify the fix

After applying the cast or validation, test the query:

sql
-- Re-run your query
SELECT * FROM your_table WHERE id::INTEGER = 123;

-- Or test in your application
npm test  -- if you have tests

Confirm that the data converts correctly and no "Invalid text representation" errors appear.

Advanced notes

PostgreSQL's strictness about type conversions differs from MySQL and SQLite, which are more lenient. This is a feature—it catches data quality issues early. If you're migrating from MySQL, this error may surface previously hidden data problems.

For bulk operations, use PostgreSQL's COPY command with the FORMAT option and data validation. For ETL pipelines, consider using staging tables where you validate data before moving it to production tables.

UUID columns are particularly strict—they require valid hexadecimal strings in the 8-4-4-4-12 pattern. Test UUIDs with: SELECT '550e8400-e29b-41d4-a716-446655440000'::UUID;

Locale differences can affect number parsing. If your database uses a different locale than your application (e.g., German locale expects "," as decimal separator), conversions may fail. Check with: SHOW lc_numeric;

Frequently asked questions

What does "22P02" mean?
PostgreSQL is strict about data type conversions. When you attempt to cast or insert a string value into a column expecting a specific type, PostgreSQL validates the input. If the string contains characters or formatting that doesn't match the target type, the database rejects the operation with a "Invalid text representation" error (SQLSTATE 22P02).
How do I fix "22P02"?
This error occurs when PostgreSQL cannot convert a text value to the expected data type (integer, boolean, date, UUID, etc.). It means the string contains invalid characters or format for the target type.
What causes "22P02"?
Casting a non-numeric string (e.g., "abc") to an integer or numeric type Passing an empty string to an integer or numeric column Attempting to cast an invalid date format (e.g., "2023-02-30") to DATE type
Is the "22P02" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading