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

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

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

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

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

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

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