我在繁重的重新排列数据表中有一些操作,到目前为止效果很好。
在一个超过 50000 行的表中,我有文本列,其中文本应该只是数字。
现在我想将其转换为整数列。
所以:
ALTER TABLE mytable ALTER COLUMN mycolumn TYPE integer;
这将产生错误 42804:*datatype_mismatch*
通过阅读文档,我找到了解决方案:
ALTER TABLE mytable ALTER COLUMN mycolumn TYPE integer USING (TRIM(mycolumn)::integer);
但我知道数据在数字顺序上可能不正确,因为这"掩盖"了错误,并且有可能(手动)编辑了列。毕竟,也许只是添加了尾随空格或进行了其他一些小的编辑。
我有数据备份。
我如何找到给定列的哪个确切单元格包含错误,以及哪个值不能通过一些适合从 pgadmin 使用的方便查询转换为 int?
请问这个查询,如果不是太复杂。
扩展@dystroy的答案,此查询应该咳出任何违规行的精确值:
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;
原答案:
如果以下方法有效:
ALTER TABLE mytable
ALTER COLUMN mycolumn TYPE integer USING (TRIM(mycolumn)::integer);
然后,您可能应该能够运行以下命令来查找垃圾箱:
select mycolumn from mytable
where mycolumn::text <> (TRIM(mycolumn)::integer)::text;