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

In Oracle you never have to cast, in Postgres it's pretty common.<br /><br />See the doc on String Operators and
Functionsalso:<br /><a
href="http://www.postgresql.org/docs/8.4/interactive/functions-string.html">http://www.postgresql.org/docs/8.4/interactive/functions-string.html</a><br
/><br/><div class="gmail_quote">On Tue, Feb 15, 2011 at 3:48 PM, Tony Capobianco <span dir="ltr"><<a
href="mailto:tcapobianco@prospectiv.com">tcapobianco@prospectiv.com</a>></span>wrote:<br /><blockquote
class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
I'maltering datatypes in several tables from numeric to integer.  In<br /> doing so, I get the following error:<br
/><br/> dw=# \d uniq_hits<br />    Table "support.uniq_hits"<br />   Column   |  Type   | Modifiers<br />
------------+---------+-----------<br/>  sourceid   | numeric |<br />  hitdate    | date    |<br />  total      |
numeric|<br />  hitdate_id | integer |<br /> Indexes:<br />    "uniq_hits_hitdateid_idx" btree (hitdate_id),
tablespace<br/> "support_idx"<br /> Tablespace: "support"<br /><br /> esave_dw=# alter table uniq_hits alter sourceid
typeint;<br /> ERROR:  integer out of range<br /><br /> Sourceid should not be more than 5 digits long.  I'm able to
perform<br/> this query on Oracle and would like something similar on postgres 8.4:<br /><br /> delete from uniq_hits
wheresourceid in (select sourceid from uniq_hits<br /> where length(sourceid) > 5);<br /><br /> I haven't had much
luckwith the length or char_length functions on<br /> postgres.<br /><br /> Thanks.<br /> Tony<br /><font
color="#888888"><br/><br /> --<br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/><br clear="all" /><br />-- <br />Peter Steinheuser<br /><a
href="mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a><br/> 

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