Thread: 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