From: Jerry Sievers <gsievers19@comcast.net>
To: Suresh Raja <suresh.rajaabc@gmail.com>
Cc: pgsql-general@postgresql.org,  pgsql-sql@postgresql.org
Subject: Re: [GENERAL] check data for datatype
References: 
 <CAJP7dtA8eerfd0KvAvNBd3806BQ6uwr2jYEcwgAbXJM_bDs5AQ@mail.gmail.com>
Date: Fri, 27 Mar 2015 13:53:09 -0500
In-Reply-To: 
 <CAJP7dtA8eerfd0KvAvNBd3806BQ6uwr2jYEcwgAbXJM_bDs5AQ@mail.gmail.com>
 (Suresh Raja's message of "Fri, 27 Mar 2015 13:08:43 -0500")
Message-ID: <86vbhm2rxm.fsf@jerry.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 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@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql