Suresh Raja <suresh.rajaabc@gmail.com> writes:
> Hi All:
>
> I have a very large table and the column type is text. I would like to convert in numeric. How can I find rows
thatdont have numbers. I would like to delete those
> rows.
begin;
set local client_min_messages to notice;
create table foo (a text);
copy foo from stdin;
1
foo
\.
create function foo (text)
returns numeric
as $$
begin
return $1::numeric;
exception when invalid_text_representation then
raise notice '%: %', sqlstate, sqlerrm;
return 'nan';
end
$$
language plpgsql;
alter table foo alter a type numeric using foo(a);
select * from foo;
--now go delete your 'nan rows
abort;
>
> Thanks,
> -Suersh Raja
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800