agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedcheck data for datatype
5+ messages / 5 participants
[nested] [flat]
* check data for datatype
@ 2015-03-27 18:08 Suresh Raja <suresh.rajaabc@gmail.com>
2015-03-27 18:14 ` Re: check data for datatype Raymond O'Donnell <rod@iol.ie>
2015-03-27 18:53 ` Re: [GENERAL] check data for datatype Jerry Sievers <gsievers19@comcast.net>
2015-04-07 16:59 ` Re: check data for datatype Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 3 replies; 5+ messages in thread
From: Suresh Raja @ 2015-03-27 18:08 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: check data for datatype
2015-03-27 18:08 check data for datatype Suresh Raja <suresh.rajaabc@gmail.com>
@ 2015-03-27 18:14 ` Raymond O'Donnell <rod@iol.ie>
2 siblings, 0 replies; 5+ messages in thread
From: Raymond O'Donnell @ 2015-03-27 18:14 UTC (permalink / raw)
To: Suresh Raja <suresh.rajaabc@gmail.com>; pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] check data for datatype
2015-03-27 18:08 check data for datatype Suresh Raja <suresh.rajaabc@gmail.com>
@ 2015-03-27 18:53 ` Jerry Sievers <gsievers19@comcast.net>
2 siblings, 0 replies; 5+ messages in thread
From: Jerry Sievers @ 2015-03-27 18:53 UTC (permalink / raw)
To: Suresh Raja <suresh.rajaabc@gmail.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: check data for datatype
2015-03-27 18:08 check data for datatype Suresh Raja <suresh.rajaabc@gmail.com>
@ 2015-04-07 16:59 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2015-04-07 21:31 ` Re: [SQL] check data for datatype Jim Nasby <Jim.Nasby@BlueTreble.com>
2 siblings, 1 reply; 5+ messages in thread
From: Gerardo Herzig @ 2015-04-07 16:59 UTC (permalink / raw)
To: Suresh Raja <suresh.rajaabc@gmail.com>; +Cc: pgsql-general@postgresql.org; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] check data for datatype
2015-03-27 18:08 check data for datatype Suresh Raja <suresh.rajaabc@gmail.com>
2015-04-07 16:59 ` Re: check data for datatype Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2015-04-07 21:31 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jim Nasby @ 2015-04-07 21:31 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; Suresh Raja <suresh.rajaabc@gmail.com>; +Cc: pgsql-general@postgresql.org; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-04-07 21:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-03-27 18:08 check data for datatype Suresh Raja <suresh.rajaabc@gmail.com>
2015-03-27 18:14 ` Raymond O'Donnell <rod@iol.ie>
2015-03-27 18:53 ` Jerry Sievers <gsievers19@comcast.net>
2015-04-07 16:59 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2015-04-07 21:31 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox