Обсуждение: Determine length of numeric field

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

Hello

probably you have to use a explicit cast

postgres=# select length(10::numeric::text);length
--------     2
(1 row)

Regards

Pavel Stehule

2011/2/15 Tony Capobianco <tcapobianco@prospectiv.com>:
> 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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Pavel,
That's perfect!
Thanks.
Tony

On Tue, 2011-02-15 at 22:04 +0100, Pavel Stehule wrote:
> Hello
> 
> probably you have to use a explicit cast
> 
> postgres=# select length(10::numeric::text);
>  length
> --------
>       2
> (1 row)
> 
> Regards
> 
> Pavel Stehule
> 
> 2011/2/15 Tony Capobianco <tcapobianco@prospectiv.com>:
> > 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
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> >
> 

Tony Capobianco <tcapobianco@prospectiv.com> writes:
> 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);

That seems like a pretty bizarre operation to apply to a number.  Why
not "where sourceid > 99999"?  Or maybe "where abs(sourceid) > 99999"
would be better.
        regards, tom lane

Tom,
That's a frighteningly easy solution.
Thanks.
Tony

On Tue, 2011-02-15 at 16:10 -0500, Tom Lane wrote:
> Tony Capobianco <tcapobianco@prospectiv.com> writes:
> > 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);
> 
> That seems like a pretty bizarre operation to apply to a number.  Why
> not "where sourceid > 99999"?  Or maybe "where abs(sourceid) > 99999"
> would be better.
> 
>             regards, tom lane
> 

On 2011-02-15, Tony Capobianco <tcapobianco@prospectiv.com> wrote:
> 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);

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

or even:  delete from uniq_hits where length(sourceid::text) > 5;

but using length on numbers is usually the wrong way.

do this instead:  delete from uniq_hits where abs(sourceid) > 2^32-1;
Which will hit all the ones that can't be converted.
You may want to do a select first to see what you're deleting.
> I haven't had much luck with the length or char_length functions on
> postgres.

The length functions only work with strings. using them on numbers is
usually the wrong thing as there is not a 1 to 1 mapping between
strings an numbers. 

Strings of length only 3 can be out of range for integer (eg: '9e9'),
(but numerics never look like that, larger floats can though)

care to guess the result of this query?
select '9000000000000000'::float, length('9000000000000000'::float::text);

-- 
⚂⚃ 100% natural