How to fix Cannot coerce in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 220 views

PostgreSQL error 42846 occurs when attempting an incompatible type conversion. Fix it by using explicit CAST() or the :: operator to convert values to compatible types.

Quick fix

Fixes 42846

-- Error example: cannot cast type text to integer
SELECT * FROM users WHERE id = '123'; -- This fails if id is integer
  1. Identify the problematic query in error logs
  2. Check column types using psql introspection
  3. Apply explicit type casting using :: operator
  4. Use CAST() function as alternative syntax
postgresql — error
-- Error example: cannot cast type text to integer
SELECT * FROM users WHERE id = '123'; -- This fails if id is integer
Cannot coerce

What this error means

The "Cannot coerce" error (code 42846) means PostgreSQL cannot automatically convert a value from one data type to another. This happens when you attempt an operation between incompatible types—for example, comparing a text string directly to an integer column, or inserting a string into an integer field without conversion. PostgreSQL has a strict type system and will not implicitly coerce types unless an explicit cast or a valid conversion rule exists in the system catalogs. The error indicates that the system has no registered conversion path for the types involved in your operation.

Typical symptoms

  • Queries fail with error code 42846 when joining tables on mismatched column types
  • INSERT or UPDATE operations fail when supplying a wrong data type
  • Comparison operations between different types produce type coercion errors
  • Function calls fail because arguments do not match parameter types
  • JSON or other complex types cannot be directly converted to expected types

Common causes

  1. Attempting to join or compare columns with different data types (integer vs text, bigint vs integer)
  2. Inserting string literals into numeric columns without explicit casting
  3. Using VARCHAR or TEXT where BIGINT or other numeric types are expected
  4. Passing incorrect argument types to PostgreSQL functions or stored procedures
  5. Type mismatch in WHERE clauses, JOIN conditions, or aggregate functions
  6. Using legacy or user-defined types that lack implicit conversion rules

How to fix "Cannot coerce"

1Identify the problematic query in error logs

Review PostgreSQL error logs or application error messages to locate the exact SQL statement causing the error. The error message typically includes the data types involved and the operation that failed.

bash
-- Error example: cannot cast type text to integer
SELECT * FROM users WHERE id = '123'; -- This fails if id is integer
2Check column types using psql introspection

Connect to your PostgreSQL database and inspect the data types of the columns involved:

sql
-- For tables:
\d table_name

-- For functions:
\df+ function_name

-- For specific column info:
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'your_table';
3Apply explicit type casting using :: operator

Modify your query to explicitly cast values to the required type using the :: operator. This is the PostgreSQL-specific syntax:

sql
-- Cast string to integer
SELECT * FROM users WHERE id = '123'::integer;

-- Cast integer to text for comparison
SELECT * FROM logs WHERE user_id::text = '456';

-- Cast in JOIN conditions
SELECT * FROM orders o
JOIN customers c ON o.customer_id::bigint = c.id::bigint;
4Use CAST() function as alternative syntax

If preferred, use the standard SQL CAST() function instead of the :: operator:

sql
-- Equivalent to the :: syntax above
SELECT * FROM users WHERE id = CAST('123' AS INTEGER);

SELECT * FROM logs WHERE CAST(user_id AS TEXT) = '456';

INSERT INTO numbers(val) VALUES (CAST('789' AS INTEGER));
5Fix INSERT/UPDATE type mismatches

For data modification operations, ensure values match the column type:

sql
-- Wrong: Inserting text into integer column
INSERT INTO products(price) VALUES ('99.99');

-- Correct: Cast to proper numeric type
INSERT INTO products(price) VALUES ('99.99'::numeric);
INSERT INTO products(price) VALUES (CAST('99.99' AS NUMERIC));

-- For VARCHAR columns expecting numbers
INSERT INTO data(code) VALUES (123::varchar);
6Update schema design for type consistency

For long-term fixes, ensure schema consistency:

sql
-- Alter column type if many casts are needed
ALTER TABLE users ALTER COLUMN id TYPE BIGINT;

-- Or convert existing data before altering
ALTER TABLE logs ALTER COLUMN timestamp TYPE TIMESTAMP USING timestamp::TIMESTAMP;

-- Verify foreign key types match:
-- If orders.customer_id is INT but customers.id is BIGINT, align them
ALTER TABLE orders ALTER COLUMN customer_id TYPE BIGINT;

Advanced notes

PostgreSQL's type system uses the pg_cast system catalog to determine valid conversions. Some casts are explicit-only (require CAST/::), while others are implicit. When you encounter a 42846 error, it means no conversion rule exists between those types, or the rule is explicit-only and wasn't invoked. You can query pg_cast to see available conversions: SELECT castsource::regtype, casttarget::regtype FROM pg_cast; If you need a custom cast between user-defined types, use CREATE CAST (requires superuser). For performance: explicit casts in WHERE clauses may impact query optimization—add indexes on cast expressions if filtering becomes slow: CREATE INDEX idx_user_id_text ON logs((user_id::text)); For JSON/JSONB coercion: casting between JSON types and other types is restricted; use -> operator for extraction instead: SELECT data->>'field' FROM json_table;

Frequently asked questions

What does "42846" mean?
The "Cannot coerce" error (code 42846) means PostgreSQL cannot automatically convert a value from one data type to another. This happens when you attempt an operation between incompatible types—for example, comparing a text string directly to an integer column, or inserting a string into an integer field without conversion. PostgreSQL has a strict type system and will not implicitly coerce types unless an explicit cast or a valid conversion rule exists in the system catalogs.
How do I fix "42846"?
PostgreSQL error 42846 occurs when attempting an incompatible type conversion. Fix it by using explicit CAST() or the :: operator to convert values to compatible types.
What causes "42846"?
Attempting to join or compare columns with different data types (integer vs text, bigint vs integer) Inserting string literals into numeric columns without explicit casting Using VARCHAR or TEXT where BIGINT or other numeric types are expected
Is the "42846" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading