column cannot be cast to type

This error occurs when you try to change a column's data type but PostgreSQL can't convert existing data automatically.

The cannot be cast error occurs when PostgreSQL can't automatically convert a column's data type.

Understanding the Error

ERROR: column "price" cannot be cast automatically to type integer
HINT: You might need to specify "USING price::integer".

The data in the column can't be directly converted to the new type.

Common Causes

1. Text to Integer Conversion

SQL

-- Column contains non-numeric text
ALTER TABLE products ALTER COLUMN price TYPE INTEGER;  -- Error if contains 'N/A'

2. Incompatible Type Change

SQL

-- Boolean to integer
ALTER TABLE users ALTER COLUMN active TYPE INTEGER;  -- Error!

3. Precision Loss

SQL

-- NUMERIC to INTEGER (loses decimals)
ALTER TABLE prices ALTER COLUMN amount TYPE INTEGER;  -- Error!

How to Fix It

Solution 1: Use USING Clause

SQL

-- Explicit conversion with USING
ALTER TABLE products
ALTER COLUMN price TYPE INTEGER
USING price::INTEGER;

-- With rounding
ALTER TABLE prices
ALTER COLUMN amount TYPE INTEGER
USING ROUND(amount)::INTEGER;

Solution 2: Handle Special Values

SQL

-- Convert text column to integer, handling non-numeric values
ALTER TABLE products
ALTER COLUMN price TYPE INTEGER
USING (
  CASE
    WHEN price ~ '^[0-9]+$' THEN price::INTEGER
    ELSE 0
  END
);

Solution 3: Clean Data First

SQL

-- Update invalid values before type change
UPDATE products SET price = '0' WHERE price !~ '^[0-9]+$';

-- Then change type
ALTER TABLE products ALTER COLUMN price TYPE INTEGER USING price::INTEGER;

Solution 4: Boolean to Integer

SQL

ALTER TABLE users
ALTER COLUMN active TYPE INTEGER
USING CASE WHEN active THEN 1 ELSE 0 END;

Solution 5: Create New Column

SQL

-- Add new column with correct type
ALTER TABLE products ADD COLUMN price_new INTEGER;

-- Copy and convert data
UPDATE products SET price_new = price::INTEGER;

-- Drop old, rename new
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_new TO price;

Solution 6: Date/Timestamp Conversion

SQL

-- Text to date
ALTER TABLE events
ALTER COLUMN event_date TYPE DATE
USING event_date::DATE;

-- Timestamp to date
ALTER TABLE logs
ALTER COLUMN created_at TYPE DATE
USING created_at::DATE;

-- Date to timestamp
ALTER TABLE events
ALTER COLUMN event_date TYPE TIMESTAMP
USING event_date::TIMESTAMP;

Common USING Patterns

SQL

-- String to integer
USING column_name::INTEGER

-- Numeric to integer (truncate)
USING column_name::INTEGER

-- Numeric to integer (round)
USING ROUND(column_name)::INTEGER

-- Boolean to text
USING CASE WHEN column_name THEN 'yes' ELSE 'no' END

-- Text to boolean
USING column_name IN ('true', 't', 'yes', '1')

-- Null handling
USING COALESCE(column_name, 0)::INTEGER

Best Practices

Backup data before type changes
Test conversion on copy of production
Handle edge cases in USING clause
Clean data first if many invalid values
Document the conversion for audit purposes