Обсуждение: 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 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