How to fix Invalid character value for cast in PostgreSQL

-- This will fail:
SELECT CAST('abc123' AS INTEGER);
-- ERROR: invalid input syntax for integer: "abc123"

-- Check for non-numeric characters
SELECT * FROM users WHERE phone ~ '[^0-9]';

-- Remove non-numeric characters before casting
SELECT CAST(REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS INTEGER) FROM users;

-- Check if string matches date format
SELECT * FROM events WHERE date_str !~ '\d{4}-\d{2}-\d{2}';

-- This returns NULL instead of erroring
SELECT TRY_CAST('abc123' AS INTEGER);
-- Result: NULL

-- Use with COALESCE to provide a default
SELECT COALESCE(TRY_CAST(value AS INTEGER), 0) FROM my_table;

-- Safe integer casting function
CREATE OR REPLACE FUNCTION safe_cast_to_int(text)
RETURNS INTEGER AS $$
BEGIN
  RETURN CAST($1 AS INTEGER);
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use it like TRY_CAST
SELECT safe_cast_to_int('abc123');
-- Result: NULL

-- Only cast if the string looks numeric
SELECT 
  CASE 
    WHEN phone ~ ''^ [0-9]{10}$'' THEN CAST(phone AS BIGINT)
    ELSE NULL
  END AS phone_id
FROM users;

-- For dates, validate format first
SELECT
  CASE
    WHEN date_str ~ '\d{4}-\d{2}-\d{2}' THEN CAST(date_str AS DATE)
    ELSE NULL
  END AS event_date
FROM events;

-- Remove or replace NULL before casting
SELECT CAST(COALESCE(age, '0') AS INTEGER) FROM users;

-- Or use NULLIF to skip empty strings
SELECT CAST(NULLIF(age, '') AS INTEGER) FROM users;

-- This will fail if column contains non-numeric data:
ALTER TABLE orders ALTER COLUMN quantity TYPE INTEGER;

-- Use USING to safely convert with validation:
ALTER TABLE orders 
ALTER COLUMN quantity TYPE INTEGER 
USING CASE 
  WHEN quantity ~ ''^ [0-9]+$'' THEN CAST(quantity AS INTEGER)
  ELSE 0
END;

-- Or with TRY_CAST (PostgreSQL 16+):
ALTER TABLE orders 
ALTER COLUMN quantity TYPE INTEGER 
USING TRY_CAST(quantity AS INTEGER);