Subject: Determine length of numeric field
From: Tony Capobianco <tcapobianco@prospectiv.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Date: Tue, 15 Feb 2011 15:48:35 -0500
Message-ID: <1297802915.1703.13.camel@tony1.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

I'm altering datatypes in several tables from numeric to integer.  In
doing so, I get the following error:

dw=# \d uniq_hits
    Table "support.uniq_hits"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 sourceid   | numeric | 
 hitdate    | date    | 
 total      | numeric | 
 hitdate_id | integer | 
Indexes:
    "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace
"support_idx"
Tablespace: "support"

esave_dw=# alter table uniq_hits alter sourceid type int;
ERROR:  integer out of range

Sourceid should not be more than 5 digits long.  I'm able to perform
this query on Oracle and would like something similar on postgres 8.4:

delete from uniq_hits where sourceid in (select sourceid from uniq_hits
where length(sourceid) > 5);

I haven't had much luck with the length or char_length functions on
postgres.

Thanks.
Tony