pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDetermine length of numeric field
7+ messages / 5 participants
[nested] [flat]
* Determine length of numeric field
@ 2011-02-15 20:48 Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 4 replies; 7+ messages in thread
From: Tony Capobianco @ 2011-02-15 20:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Determine length of numeric field
@ 2011-02-15 21:04 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2011-02-15 21:04 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Determine length of numeric field
@ 2011-02-15 21:08 Peter Steinheuser <psteinheuser@myyearbook.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 0 replies; 7+ messages in thread
From: Peter Steinheuser @ 2011-02-15 21:08 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: pgsql-sql
In Oracle you never have to cast, in Postgres it's pretty common.
See the doc on String Operators and Functions also:
http://www.postgresql.org/docs/8.4/interactive/functions-string.html
On Tue, Feb 15, 2011 at 3:48 PM, 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);
>
> 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
>
--
Peter Steinheuser
psteinheuser@myyearbook.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Determine length of numeric field
@ 2011-02-15 21:09 Tony Capobianco <tcapobianco@prospectiv.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tony Capobianco @ 2011-02-15 21:09 UTC (permalink / raw)
To: 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
> >
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Determine length of numeric field
@ 2011-02-15 21:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2011-02-15 21:10 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Determine length of numeric field
@ 2011-02-15 21:14 Tony Capobianco <tcapobianco@prospectiv.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tony Capobianco @ 2011-02-15 21:14 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Determine length of numeric field
@ 2011-02-15 23:32 Jasen Betts <jasen@xnet.co.nz>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 0 replies; 7+ messages in thread
From: Jasen Betts @ 2011-02-15 23:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-02-15 23:32 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-15 20:48 Determine length of numeric field Tony Capobianco <tcapobianco@prospectiv.com>
2011-02-15 21:04 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-02-15 21:09 ` Tony Capobianco <tcapobianco@prospectiv.com>
2011-02-15 21:08 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2011-02-15 21:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-15 21:14 ` Tony Capobianco <tcapobianco@prospectiv.com>
2011-02-15 23:32 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox