Re: [GENERAL] check data for datatype

Suresh Raja <suresh(dot)rajaabc(at)gmail(dot)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 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(dot)consulting(at)comcast(dot)net
p: 312.241.7800