agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Jerry Sievers <gsievers19@comcast.net>
To: Suresh Raja <suresh.rajaabc@gmail.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [GENERAL] check data for datatype
Date: Fri, 27 Mar 2015 13:53:09 -0500
Message-ID: <86vbhm2rxm.fsf@jerry.enova.com> (raw)
In-Reply-To: <CAJP7dtA8eerfd0KvAvNBd3806BQ6uwr2jYEcwgAbXJM_bDs5AQ@mail.gmail.com>
References: <CAJP7dtA8eerfd0KvAvNBd3806BQ6uwr2jYEcwgAbXJM_bDs5AQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (5+ messages)  latest in thread

Message-ID: <86vbhm2rxm.fsf@jerry.enova.com>
Permalink:  ../86vbhm2rxm.fsf@jerry.enova.com/
Also on:    postgresql.org/message-id/86vbhm2rxm.fsf@jerry.enova.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gsievers19@comcast.net, suresh.rajaabc@gmail.com, pgsql-general@postgresql.org
  Subject: Re: [GENERAL] check data for datatype
  In-Reply-To: <86vbhm2rxm.fsf@jerry.enova.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox