Suresh Raja <suresh.rajaabc@xxxxxxxxx> 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 that dont 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@xxxxxxxxxxx
p: 312.241.7800
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
