How to fix Invalid text representation in PostgreSQL

\d your_table_name

-- 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 = '';

-- 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;

-- 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;

// 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 }
});

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

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