Thread: check data for datatype

On 27/03/2015 18:08, Suresh Raja wrote:
>     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.

Use a regular expression:

  select <whatever> from <the table> where <the column> ~ <regexp>

http://www.postgresql.org/docs/9.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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

I guess that could need something like (untested)

delete from bigtable text_column !~ '^[0-9][0-9]*$';


HTH
Gerardo

----- Mensaje original -----
> De: "Suresh Raja" <suresh.rajaabc@gmail.com>
> Para: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
> Enviados: Viernes, 27 de Marzo 2015 15:08:43
> Asunto: [SQL] check data for datatype
>
>
>
>
>
>
>
>
> 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.
>
>
> Thanks,
> -Suersh Raja

On 4/7/15 11:59 AM, Gerardo Herzig wrote:
> I guess that could need something like (untested)
>
> delete from bigtable text_column !~ '^[0-9][0-9]*$';

Won't work for...

.1
-1
1.1e+5
...

Really you need to do something like what Jerry suggested if you want
this to be robust.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com