Find what row holds a value which cannot be cast to integer

Question

I have some operations in heavy rearanging data tables which goes good so far.
In one table with more than 50000 rows I have text column where text should be numbers only.
Now I would like to convert it to integer column.
So:

ALTER TABLE mytable ALTER COLUMN mycolumn TYPE integer;

That produces an error 42804: *datatype_mismatch*

By reading docs I find solution:

ALTER TABLE mytable ALTER COLUMN mycolumn TYPE integer USING (TRIM(mycolumn)::integer); 

But I am aware that data may not be correct in mean of number order since this "masks" an error and there is possibility that column was edited (by hand). After all, maybe is only trailing space added or some other minor editing was made.

I have backup of data.
How would I find which exact cell of given column contain an error and which value cannot be casted to int with some handy query suitable for use from pgadmin?

Please that query if is not complicated too much.

Answer 1

Expanding on @dystroy's answer, this query should cough the precise value of any offending rows:

CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS INTEGER AS $$
BEGIN
    BEGIN
        RETURN v_input::INTEGER;
    EXCEPTION WHEN OTHERS THEN
        RAISE EXCEPTION 'Invalid integer value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
END;
$$ LANGUAGE plpgsql;

---

Original answer:

If the following works:

ALTER TABLE mytable
ALTER COLUMN mycolumn TYPE integer USING (TRIM(mycolumn)::integer);

Then you should probably be able to run the following to locate the trash:

select mycolumn from mytable
where mycolumn::text <> (TRIM(mycolumn)::integer)::text;