What causes "Column has type X but expression has type Y"?

Attempting to insert a string value into an integer, bigint, or numeric column without casting Assigning a numeric value to a varchar or text column without conversion Using quoted numbers or booleans that PostgreSQL interprets as text instead of numeric/boolean types

Is the "Column has type X but expression has type Y" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

How to fix Column has type X but expression has type Y in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 162 views

PostgreSQL error 42804 occurs when you attempt to insert, update, or assign a value of one data type to a column with an incompatible data type. This strict type checking requires explicit casting to resolve mismatches between the target column type and the value being assigned.

Quick fix

Fixes Column has type X but expression has type Y

\d your_table_name

Identify the exact column and value types involved
Review the problematic SQL statement
Use explicit casting with the :: operator
Use the CAST() function for portability

postgresql — error

$\d your_table_name
Column has type X but expression has type Y

What this error means

PostgreSQL enforces strict type checking and does not allow implicit type conversions in most cases. When you try to assign a value to a column whose data type does not match the value type, PostgreSQL raises this error (SQLSTATE 42804). For example, you cannot insert a text string into an integer column, assign a boolean value to a varchar column, or use a numeric value where a date is expected without explicit casting. This error commonly occurs in INSERT/UPDATE statements, function calls, expressions, and comparisons.

Typical symptoms

INSERT statement fails with "column X is of type Y but expression is of type Z"
UPDATE statement fails when assigning values of mismatched types
Default value assignment fails during CREATE TABLE or ALTER TABLE operations
Function calls fail due to argument type mismatch
Type casting operations fail with "cannot be cast automatically" message
UNION queries fail when column types do not align
JOIN conditions fail when comparing columns of different types

Common causes

Attempting to insert a string value into an integer, bigint, or numeric column without casting
Assigning a numeric value to a varchar or text column without conversion
Using quoted numbers or booleans that PostgreSQL interprets as text instead of numeric/boolean types
Mismatched column types between source and target columns in INSERT...SELECT statements
Function parameters that do not match the function signature due to type differences
Default values that are incompatible with the column data type
JOIN or WHERE conditions comparing columns of different types without explicit casting
CSV import or data loading treating numeric strings as text instead of numbers

How to fix "Column has type X but expression has type Y"

1Identify the exact column and value types involved

Use the \d command in psql to inspect the table structure and identify the column data type:

sql

\d your_table_name

Review the error message carefully, as it specifies which column has which type and what type the expression is. For example: "column id is of type integer but expression is of type character varying" tells you the column is integer and you're trying to assign a varchar.

2Review the problematic SQL statement

Identify the exact INSERT, UPDATE, or assignment statement causing the error:

sql

-- WRONG: Inserting varchar string into integer column
INSERT INTO users (id, name) VALUES ('123', 'John');

-- WRONG: Assigning varchar to integer in UPDATE
UPDATE products SET product_id = '456' WHERE id = 1;

-- WRONG: Default value type mismatch
ALTER TABLE orders ADD COLUMN status integer DEFAULT 'pending';

Look for places where string values (quoted text) are being used where numeric, boolean, or date types are expected.

3Use explicit casting with the :: operator

PostgreSQL provides the :: typecast operator for explicit type conversions. This is the most common and recommended solution:

sql

-- CORRECT: Cast string to integer
INSERT INTO users (id, name) VALUES ('123'::integer, 'John');

-- CORRECT: Cast numeric to varchar
UPDATE products SET product_id = 456::varchar WHERE id = 1;

-- CORRECT: Cast string to date
INSERT INTO events (event_name, event_date) VALUES ('Conference', '2024-12-25'::date);

-- CORRECT: Cast string to boolean
UPDATE settings SET enabled = 'true'::boolean WHERE id = 1;

Place the :: operator immediately after the value, followed by the target data type.

4Use the CAST() function for portability

Alternatively, use the standard SQL CAST() function, which is more verbose but portable across databases:

sql

INSERT INTO users (id, name) VALUES (CAST('123' AS integer), 'John');

UPDATE products SET price = CAST('99.99' AS numeric) WHERE id = 1;

INSERT INTO events (event_date) VALUES (CAST('2024-12-25' AS date));

UPDATE accounts SET verified = CAST('yes' AS boolean) WHERE id = 1;

The CAST() syntax is standard SQL and works similarly across different database systems.

5Fix INSERT...SELECT statements with mismatched types

When using INSERT...SELECT with different column types, cast the source columns to match the target:

sql

-- WRONG: Source columns have different types than target
INSERT INTO archive_users (id, status, created_date)
SELECT id, status, created_date FROM staging_users;
-- Fails if staging_users.id is varchar but archive_users.id is integer

-- CORRECT: Cast source columns to match target types
INSERT INTO archive_users (id, status, created_date)
SELECT id::integer, status::varchar, created_date::timestamp FROM staging_users;

Always verify that column order and types match between source and destination tables.

6Fix default value type mismatches in ALTER TABLE

When adding a column with a default value or changing a column type, ensure the default is compatible:

sql

-- WRONG: Default value type does not match column type
ALTER TABLE orders ADD COLUMN status varchar DEFAULT 1;

-- CORRECT: Default value matches column type
ALTER TABLE orders ADD COLUMN status varchar DEFAULT 'pending';

-- CORRECT: When changing column type, cast the default
ALTER TABLE settings
ALTER COLUMN timeout TYPE integer USING timeout::integer,
ALTER COLUMN timeout SET DEFAULT 30;

Use the USING clause to convert existing column values when changing types.

7Fix function calls with argument type mismatches

Ensure function arguments match their expected data types:

sql

-- WRONG: Passing varchar where integer is expected
SELECT substring('hello', '2', '4');
-- substring() expects integers for position and length

-- CORRECT: Cast string arguments to the correct type
SELECT substring('hello', '2'::integer, '4'::integer);

-- CORRECT: Use proper types from the start
SELECT substring('hello', 2, 4);

Check the function documentation to understand required parameter types.

8Verify JOIN conditions use matching types

When joining tables, ensure the join condition columns have compatible types:

sql

-- WRONG: Joining integer column to varchar column
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Fails if customer_id is varchar but c.id is integer

-- CORRECT: Cast to a common type
SELECT * FROM orders o
JOIN customers c ON o.customer_id::integer = c.id;

-- BETTER: Use consistent types in table design
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Both columns defined as integer from the start

It is better to fix this at the schema level than in every query.

9Test your fixes with a simple query

After making changes, verify the fix works with a simple SELECT or INSERT:

sql

-- Test the fix
INSERT INTO users (id, name) VALUES ('123'::integer, 'John');
SELECT * FROM users WHERE id = 123;

-- Verify the data was inserted correctly
SELECT id, pg_typeof(id), name FROM users WHERE id = 123;

The pg_typeof() function is helpful for verifying data types. Once the simple query works, apply the same casting logic to your full application code.

Advanced notes

PostgreSQL's strict type checking prevents silent data corruption from accidental conversions. When importing CSV or JSON data, parse numeric values as numbers, not strings. For high-performance applications, avoid type casting in WHERE clauses on large tables (casts can prevent index usage); instead, store data in the correct type or create indexes on cast expressions. When casting to incompatible types, be aware of data loss—casting a large integer to smallint will silently truncate. Know that some casts are reversible (integer to text and back), while others lose information (text with letters to integer). For custom PostgreSQL types (enums, domains, ranges, composite types), explicit casting is required. In application code, use prepared statements and proper type binding instead of string concatenation to avoid type issues.

Frequently asked questions

What does "Column has type X but expression has type Y" mean?: PostgreSQL enforces strict type checking and does not allow implicit type conversions in most cases. When you try to assign a value to a column whose data type does not match the value type, PostgreSQL raises this error (SQLSTATE 42804). For example, you cannot insert a text string into an integer column, assign a boolean value to a varchar column, or use a numeric value where a date is expected without explicit casting.
How do I fix "Column has type X but expression has type Y"?: PostgreSQL error 42804 occurs when you attempt to insert, update, or assign a value of one data type to a column with an incompatible data type. This strict type checking requires explicit casting to resolve mismatches between the target column type and the value being assigned.
What causes "Column has type X but expression has type Y"?: Attempting to insert a string value into an integer, bigint, or numeric column without casting Assigning a numeric value to a varchar or text column without conversion Using quoted numbers or booleans that PostgreSQL interprets as text instead of numeric/boolean types
Is the "Column has type X but expression has type Y" error serious?: It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.