pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedisnumeric() function?
29+ messages / 13 participants
[nested] [flat]
* isnumeric() function?
@ 2004-04-29 22:46 Yudie <yudie@axiontech.com>
2004-04-30 07:14 ` Re: isnumeric() function? CoL <col@mportal.hu>
2004-04-30 14:11 ` Re: isnumeric() function? Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 2 replies; 29+ messages in thread
From: Yudie @ 2004-04-29 22:46 UTC (permalink / raw)
To: pgsql-sql
What is isnumeric function in postgresql?
I'm using psql version 7.2.2
thanks
Yudie
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: isnumeric() function?
2004-04-29 22:46 isnumeric() function? Yudie <yudie@axiontech.com>
@ 2004-04-30 07:14 ` CoL <col@mportal.hu>
1 sibling, 0 replies; 29+ messages in thread
From: CoL @ 2004-04-30 07:14 UTC (permalink / raw)
To: pgsql-sql
hi,
Yudie wrote:
> What is isnumeric function in postgresql?
> I'm using psql version 7.2.2
> thanks
probably somebody write a function called isnumeric for you :) So it
must be a user defined function.
C.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: isnumeric() function?
2004-04-29 22:46 isnumeric() function? Yudie <yudie@axiontech.com>
@ 2004-04-30 14:11 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-04-30 15:29 ` Re: isnumeric() function? Yudie <yudie@axiontech.com>
2004-04-30 16:24 ` Re: isnumeric() function? Frank Bax <fbax@sympatico.ca>
1 sibling, 2 replies; 29+ messages in thread
From: Jeff Eckermann @ 2004-04-30 14:11 UTC (permalink / raw)
To: Yudie <yudie@axiontech.com>; pgsql-sql
--- Yudie <yudie@axiontech.com> wrote:
> What is isnumeric function in postgresql?
> I'm using psql version 7.2.2
> thanks
> Yudie
I don't think that function is included as such. But
you could do something like:
CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
SELECT $1 ~ ''^[0-9]+$''
' LANGUAGE 'sql';
Note that you would need to create this function for
every parameter datatype that you would intend to use,
or else be prepared to cast your input datatype as
appropriate.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: isnumeric() function?
2004-04-29 22:46 isnumeric() function? Yudie <yudie@axiontech.com>
2004-04-30 14:11 ` Re: isnumeric() function? Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2004-04-30 15:29 ` Yudie <yudie@axiontech.com>
1 sibling, 0 replies; 29+ messages in thread
From: Yudie @ 2004-04-30 15:29 UTC (permalink / raw)
To: pgsql-sql
Great the function works, but what does it means?
SELECT $1 ~ ''^[0-9]+$''
Yudie
----- Original Message -----
From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
To: "Yudie" <yudie@axiontech.com>; <pgsql-sql@postgresql.org>
Sent: Friday, April 30, 2004 9:11 AM
Subject: Re: [SQL] isnumeric() function?
--- Yudie <yudie@axiontech.com> wrote:
> What is isnumeric function in postgresql?
> I'm using psql version 7.2.2
> thanks
> Yudie
I don't think that function is included as such. But
you could do something like:
CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
SELECT $1 ~ ''^[0-9]+$''
' LANGUAGE 'sql';
Note that you would need to create this function for
every parameter datatype that you would intend to use,
or else be prepared to cast your input datatype as
appropriate.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: isnumeric() function?
2004-04-29 22:46 isnumeric() function? Yudie <yudie@axiontech.com>
2004-04-30 14:11 ` Re: isnumeric() function? Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2004-04-30 16:24 ` Frank Bax <fbax@sympatico.ca>
1 sibling, 0 replies; 29+ messages in thread
From: Frank Bax @ 2004-04-30 16:24 UTC (permalink / raw)
To: pgsql-sql
At 11:29 AM 4/30/04, Yudie wrote:
>Great the function works, but what does it means?
>SELECT $1 ~ ''^[0-9]+$''
>
>Yudie
The ~ is a pattern matching operator.
^ matches beginning of string
[0-9] matches any numeric digit 0 thru 9.
+ matches one or more occurrences of what came before (digits in
this case)
$ matches end of string
The ^ and $ are important - if they were left out, the pattern would match
a string containing both numeric and non-numeric data.
You can change the + to * if you decide that an empty string should be
considered numeric.
Frank
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: isnumeric() function?
@ 2004-05-01 14:29 Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 0 replies; 29+ messages in thread
From: Jeff Eckermann @ 2004-05-01 14:29 UTC (permalink / raw)
To: Yudie <yudie@axiontech.com>; +Cc: pgsql-sql
--- Yudie <yudie@axiontech.com> wrote:
> Great the function works, but what does it means?
> SELECT $1 ~ ''^[0-9]+$''
>
> Yudie
"~" is the regular expression match operator; the text
inside the quotes is a regular expression. Look here
for a reference:
http://www.postgresql.org/docs/7.2/interactive/functions-matching.html#FUNCTIONS-REGEXP
The function definition in my note was intended as an
example; it won't necessarily do exactly what you
want. For example, it will reject a decimal point (in
that case, try SELECT $1 ~ ''^[0-9]+(\\.[0-9]+)?$'').
If you are not familiar with regular expressions,
consider spending a little time learning about them.
They are simply the best way to do pattern matching,
and if you do any amount of text processing at all,
time spent learning will repay you very well.
>
> ----- Original Message -----
> From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
> To: "Yudie" <yudie@axiontech.com>;
> <pgsql-sql@postgresql.org>
> Sent: Friday, April 30, 2004 9:11 AM
> Subject: Re: [SQL] isnumeric() function?
>
>
> --- Yudie <yudie@axiontech.com> wrote:
> > What is isnumeric function in postgresql?
> > I'm using psql version 7.2.2
> > thanks
> > Yudie
>
> I don't think that function is included as such.
> But
> you could do something like:
>
> CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
> SELECT $1 ~ ''^[0-9]+$''
> ' LANGUAGE 'sql';
>
> Note that you would need to create this function for
> every parameter datatype that you would intend to
> use,
> or else be prepared to cast your input datatype as
> appropriate.
>
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Win a $20,000 Career Makeover at Yahoo! HotJobs
> http://hotjobs.sweepstakes.yahoo.com/careermakeover
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
^ permalink raw reply [nested|flat] 29+ messages in thread
* Isnumeric function?
@ 2004-09-07 05:44 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 29+ messages in thread
From: Theo Galanakis @ 2004-09-07 05:44 UTC (permalink / raw)
To: pgsql-sql
How could you determine if a value being inserted into a varchar column is
numeric?
I was thinking of using a Regular expression to find this, something like
.. Where content ~* '^[0-9]{1,10}'
There must be an easier way like a isNumeric() function?
Theo
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-07 06:05 ` Oliver Elphick <olly@lfix.co.uk>
2004-09-08 16:47 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 29+ messages in thread
From: Oliver Elphick @ 2004-09-07 06:05 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
On Tue, 2004-09-07 at 06:44, Theo Galanakis wrote:
> How could you determine if a value being inserted into a varchar
> column is numeric?
>
> I was thinking of using a Regular expression to find this, something
> like
>
> .. Where content ~* '^[0-9]{1,10}'
~ '^[0-9]+$'
Your version only checks the beginning of the string.
> There must be an easier way like a isNumeric() function?
Not that I know of.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"For whosoever shall call upon the name of the Lord
shall be saved." Romans 10:13
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
@ 2004-09-08 16:47 ` Josh Berkus <josh@agliodbs.com>
2004-09-08 17:20 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 29+ messages in thread
From: Josh Berkus @ 2004-09-08 16:47 UTC (permalink / raw)
To: olly@lfix.co.uk, Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
Oliver, Theo:
> ~ '^[0-9]+$'
Actually, I usually do:
~ '^[0-9]+\.?[0-9]*$'
... to include decimals. However, the above assumes that there is at least a
"0" before the decimal; it would be nice to adapt it to matching a leading
decimal (i.e. .057 ) as well. Can't see any easy way, though ...
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 16:47 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
@ 2004-09-08 17:20 ` Oliver Elphick <olly@lfix.co.uk>
2004-09-08 17:48 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 29+ messages in thread
From: Oliver Elphick @ 2004-09-08 17:20 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
On Wed, 2004-09-08 at 17:47, Josh Berkus wrote:
> Oliver, Theo:
>
> > ~ '^[0-9]+$'
>
> Actually, I usually do:
>
> ~ '^[0-9]+\.?[0-9]*$'
>
> ... to include decimals. However, the above assumes that there is at least a
> "0" before the decimal; it would be nice to adapt it to matching a leading
> decimal (i.e. .057 ) as well. Can't see any easy way, though ...
~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Put on the whole armor of God, that ye may be able to
stand against the wiles of the devil."
Ephesians 6:11
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 16:47 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 17:20 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
@ 2004-09-08 17:48 ` Josh Berkus <josh@agliodbs.com>
2004-09-08 18:11 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 29+ messages in thread
From: Josh Berkus @ 2004-09-08 17:48 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
Theo, Oliver,
> Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ?
Yes, because it also matches "." , which is not a valid numeric value.
> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
Ah, the brute force approach ;-)
Actually, the above could be written:
~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
... though that still seems inelegant to me. Is there a regex expert in the
house?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 16:47 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 17:20 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 17:48 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
@ 2004-09-08 18:11 ` Oliver Elphick <olly@lfix.co.uk>
2004-09-09 01:58 ` Re: Isnumeric function? Thomas Swan <tswan@idigx.com>
0 siblings, 1 reply; 29+ messages in thread
From: Oliver Elphick @ 2004-09-08 18:11 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> Theo, Oliver,
>
> > Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ?
>
> Yes, because it also matches "." , which is not a valid numeric value.
>
> > ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
>
> Ah, the brute force approach ;-)
Nothing like using a nice big hammer!
> Actually, the above could be written:
>
> ~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
But that doesn't allow a trailing decimal point.
>
> ... though that still seems inelegant to me. Is there a regex expert in the
> house?
All the elegant approaches I can think of match the empty string. There
must be at least one digit and 0 or 1 decimal point with no other
characters permitted. If you use this as a constraint, you could make
it elegant and combine it with another constraint to exclude '' and '.'.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Put on the whole armor of God, that ye may be able to
stand against the wiles of the devil."
Ephesians 6:11
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 16:47 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 17:20 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 17:48 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 18:11 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
@ 2004-09-09 01:58 ` Thomas Swan <tswan@idigx.com>
2004-09-09 14:02 ` Re: Isnumeric function? Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 1 reply; 29+ messages in thread
From: Thomas Swan @ 2004-09-09 01:58 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: Josh Berkus <josh@agliodbs.com>; Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
Oliver Elphick wrote:
>On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
>
>
>>Theo, Oliver,
>>
>>
>>
>>>Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ?
>>>
>>>
>>Yes, because it also matches "." , which is not a valid numeric value.
>>
>>
>>
>>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
>>>
>>>
>>Ah, the brute force approach ;-)
>>
>>
>
>Nothing like using a nice big hammer!
>
>
>
Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a little cleaner?
>>Actually, the above could be written:
>>
>>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
>>
>>
>
>But that doesn't allow a trailing decimal point.
>
>
>
>>... though that still seems inelegant to me. Is there a regex expert in the
>>house?
>>
>>
>
>All the elegant approaches I can think of match the empty string. There
>must be at least one digit and 0 or 1 decimal point with no other
>characters permitted. If you use this as a constraint, you could make
>it elegant and combine it with another constraint to exclude '' and '.'.
>
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 16:47 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 17:20 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 17:48 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 18:11 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-09 01:58 ` Re: Isnumeric function? Thomas Swan <tswan@idigx.com>
@ 2004-09-09 14:02 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 0 replies; 29+ messages in thread
From: Jeff Eckermann @ 2004-09-09 14:02 UTC (permalink / raw)
To: Thomas Swan <tswan@idigx.com>; olly@lfix.co.uk; +Cc: Josh Berkus <josh@agliodbs.com>; Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)
create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)-?)\'
'language 'sql';
--- Thomas Swan <tswan@idigx.com> wrote:
> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
@ 2004-09-08 23:39 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-08 23:57 ` Re: Isnumeric function? Tom Lane <tgl@sss.pgh.pa.us>
2004-09-09 03:48 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
2004-09-09 18:46 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 29+ messages in thread
From: Theo Galanakis @ 2004-09-08 23:39 UTC (permalink / raw)
To: pgsql-sql
Thankyou all for your feedback. I actually only want to check for whole
numbers, so the ~ '^[0-9]+$' expression is good.
The issue really is that our CMS system sometimes holds the value of primary
keys within a "content" varchar column(don't ask!), which is a nightmare to
search across. I tried applying an index across the "content" varchar column
and it failed.
error: btree item size 2744 exceeds maximum 2713.
I assume I had to change some server settings to extend the maximum, however
in the end this column holds content, and even applying an index would be
incredible slow to search across hundred of thousands of "content" records
looking for a primary key.
So I came up with the following. A Insert/update trigger would call a
procedure to check to see if the content is numeric(a whole number), if so
would update an indexed integer column called (content_numeric). Which would
be the base column to search appon.
Here is the function anyway:
CREATE OR REPLACE FUNCTION update_content_node()
RETURNS trigger AS
'
begin
/* New function body */
IF NEW.content ~ \'^[0-9]+$\' THEN
NEW.content_numeric := NEW.content;
ELSE
NEW.content_numeric := null;
END IF;
RETURN NEW;
end;
'
LANGUAGE 'plpgsql' IMMUTABLE;
Does anyone have any better suggestions???
Theo
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-08 23:39 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-08 23:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 29+ messages in thread
From: Tom Lane @ 2004-09-08 23:57 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> So I came up with the following. A Insert/update trigger would call a
> procedure to check to see if the content is numeric(a whole number), if so
> would update an indexed integer column called (content_numeric). Which would
> be the base column to search appon.
> CREATE OR REPLACE FUNCTION update_content_node()
> RETURNS trigger AS
> '
> begin
> /* New function body */
> IF NEW.content ~ \'^[0-9]+$\' THEN
> NEW.content_numeric := NEW.content;
> ELSE
> NEW.content_numeric := null;
Hmm. Seems like you could get burnt by "content" that is by chance a
long string of digits --- you'd get an integer overflow error at the
attempt to assign to content_numeric. Can you make an assumption that
indexable keys are at most 9 digits? If so then
IF NEW.content ~ \'^[0-9]{1,9}$\' THEN
Or use a bigint column and crank up the number of digits appropriately.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-08 23:39 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-09 03:48 ` Greg Stark <gsstark@mit.edu>
2 siblings, 0 replies; 29+ messages in thread
From: Greg Stark @ 2004-09-09 03:48 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> error: btree item size 2744 exceeds maximum 2713.
>
> I assume I had to change some server settings to extend the maximum, however
I would guess the block size. But I'm just guessing.
> in the end this column holds content, and even applying an index would be
> incredible slow to search across hundred of thousands of "content" records
> looking for a primary key.
Perhaps you could have an indexed column that contains a crc32 hash? Then you
could do searches by comparing crc32 which make for fast efficient integer
index lookups. You should still include a comparison against the original
content column since it is possible for there to be a rare crc32 collision.
This doesn't let you do range lookups efficiently. But it does let you look up
specific values quickly even when they aren't numeric.
--
greg
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-08 23:39 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-09 18:46 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 29+ messages in thread
From: Josh Berkus @ 2004-09-09 18:46 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
Theo,
> Does anyone have any better suggestions???
Well, one suggestion would be to take a machete to your application. Putting
key references and text data in the same column? Sheesh.
If that's not an option, in addition to the approach you've taken, you could
also do a partial index on the appropriate numeric values:
CREATE INDEX idx_content_numeric ON botched_table(content)
WHERE content ~ '^[0-9]{1,9}$';
However, this approach may be more/less effective that the segregation
approach you've already taken.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
@ 2004-09-09 00:02 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
0 siblings, 0 replies; 29+ messages in thread
From: Theo Galanakis @ 2004-09-09 00:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thanks Tom,
Actually I did not attach the latest function, I did have a limit of 9
numerical characters, found that out when I applied the update to move all
current numerical values to that column.
Theo
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, 9 September 2004 9:57 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?
Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> So I came up with the following. A Insert/update trigger would call a
> procedure to check to see if the content is numeric(a whole number),
> if so would update an indexed integer column called (content_numeric).
> Which would be the base column to search appon.
> CREATE OR REPLACE FUNCTION update_content_node()
> RETURNS trigger AS
> '
> begin
> /* New function body */
> IF NEW.content ~ \'^[0-9]+$\' THEN
> NEW.content_numeric := NEW.content;
> ELSE
> NEW.content_numeric := null;
Hmm. Seems like you could get burnt by "content" that is by chance a long
string of digits --- you'd get an integer overflow error at the attempt to
assign to content_numeric. Can you make an assumption that indexable keys
are at most 9 digits? If so then
IF NEW.content ~ \'^[0-9]{1,9}$\' THEN
Or use a bigint column and crank up the number of digits appropriately.
regards, tom lane
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
@ 2004-09-09 15:01 Passynkov, Vadim <Vadim.Passynkov@pathcom.com>
0 siblings, 0 replies; 29+ messages in thread
From: Passynkov, Vadim @ 2004-09-09 15:01 UTC (permalink / raw)
To: 'Jeff Eckermann' <jeff_eckermann@yahoo.com>; Thomas Swan <tswan@idigx.com>; olly@lfix.co.uk; +Cc: Josh Berkus <josh@agliodbs.com>; Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
How about this
CREATE OR REPLACE FUNCTION is_numeric ( text ) RETURNS bool AS '
if { [string is integer $1] || [string is double $1] } {
return true
}
return false
' LANGUAGE 'pltcl' IMMUTABLE;
SELECT is_numeric ( '-1' );
is_numeric
------------
t
(1 row)
SELECT is_numeric ( '+1e-1' );
is_numeric
------------
t
(1 row)
SELECT is_numeric ( '1.1.1' );
is_numeric
------------
f
(1 row)
--
Vadim Passynkov
-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Sent: Thursday, September 09, 2004 10:02 AM
To: Thomas Swan; olly@lfix.co.uk
Cc: Josh Berkus; Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?
Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)
create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)
-?)\'
'language 'sql';
--- Thomas Swan <tswan@idigx.com> wrote:
> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
@ 2004-09-10 00:20 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-10 02:21 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-10 04:48 ` Re: Isnumeric function? sad <sad@bankir.ru>
0 siblings, 2 replies; 29+ messages in thread
From: Theo Galanakis @ 2004-09-10 00:20 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: pgsql-sql
I was just thinking, wouldn't it be great if the pg community had a site
where anyone could contribute their generic functions, or request for a
particular function.
Cold Fusion has a cflib.org, perhaps a pglib.org?
-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Sent: Friday, 10 September 2004 12:02 AM
To: Thomas Swan; olly@lfix.co.uk
Cc: Josh Berkus; Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?
Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)
create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)
-?)\'
'language 'sql';
--- Thomas Swan <tswan@idigx.com> wrote:
> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-10 00:20 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-10 02:21 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 29+ messages in thread
From: Josh Berkus @ 2004-09-10 02:21 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: pgsql-sql
Theo,
> I was just thinking, wouldn't it be great if the pg community had a site
> where anyone could contribute their generic functions, or request for a
> particular function.
In theory, this is supposed to be a feature of pgFoundry.org. However, there
is a bug in gForge that prevents us from using it right now, and fixing the
bug is complicated.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-10 00:20 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-10 04:48 ` sad <sad@bankir.ru>
1 sibling, 0 replies; 29+ messages in thread
From: sad @ 2004-09-10 04:48 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: pgsql-sql
On Friday 10 September 2004 04:20, Theo Galanakis wrote:
> I was just thinking, wouldn't it be great if the pg community had a site
> where anyone could contribute their generic functions, or request for a
> particular function.
i vote positive.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
@ 2004-09-10 02:50 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-10 05:39 ` Re: Isnumeric function? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-10 06:43 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 29+ messages in thread
From: Theo Galanakis @ 2004-09-10 02:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Josh,
I agree with the machete technique, unfortunately The structure is inplace
and a work-around was required.
I created the Index you specified, however it chooses to run a seq scan on
the column rather than a Index scan. How can you force it to use that
Index..
CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
'^[0-9]{1,9}$';
select * from botched_table where content = 200::integer
Theo
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Friday, 10 September 2004 4:46 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?
Theo,
> Does anyone have any better suggestions???
Well, one suggestion would be to take a machete to your application.
Putting
key references and text data in the same column? Sheesh.
If that's not an option, in addition to the approach you've taken, you could
also do a partial index on the appropriate numeric values:
CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
'^[0-9]{1,9}$';
However, this approach may be more/less effective that the segregation
approach you've already taken.
--
Josh Berkus
Aglio Database Solutions
San Francisco
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-10 02:50 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-10 05:39 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 29+ messages in thread
From: Achilleus Mantzios @ 2004-09-10 05:39 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: 'Josh Berkus' <josh@agliodbs.com>; pgsql-sql
O Theo Galanakis Ýãñáøå óôéò Sep 10, 2004 :
>
>
> Josh,
>
> I agree with the machete technique, unfortunately The structure is inplace
> and a work-around was required.
>
> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> select * from botched_table where content = 200::integer
EXPLAIN ANALYZE is your friend.
VACUUM [FULL] ANALYZE also.
Try with 200::text
In the end if there is an option for the planner to use the index
but he doesn't, then maybe its not worth it.
>
> Theo
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: Friday, 10 September 2004 4:46 AM
> To: Theo Galanakis; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Isnumeric function?
>
>
> Theo,
>
> > Does anyone have any better suggestions???
>
> Well, one suggestion would be to take a machete to your application.
> Putting
> key references and text data in the same column? Sheesh.
>
> If that's not an option, in addition to the approach you've taken, you could
>
> also do a partial index on the appropriate numeric values:
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> However, this approach may be more/less effective that the segregation
> approach you've already taken.
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-10 02:50 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-10 06:43 ` Greg Stark <gsstark@mit.edu>
2004-09-10 07:02 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 29+ messages in thread
From: Greg Stark @ 2004-09-10 06:43 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: 'Josh Berkus' <josh@agliodbs.com>; pgsql-sql
Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> select * from botched_table where content = 200::integer
You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
to match the clause in the partial index pretty closely.
perhaps you would find it convenient to make a view of
select * from botched_table where content ~ '^[0-9]{1,9}$'
and then just always select these values from that view.
Also the "::integer" is useless. It actually gets cast to text here anyways.
The index is on the text contents of the content column.
You might consider making the index a functional index on content::integer
instead. I suspect that would be faster and smaller than an index on the text
version of content:
slo=> create table botched_table (content text);
CREATE TABLE
slo=> create index idx_botched_table on botched_table ((content::integer)) where content ~ '^[0-9]{1,9}$';
CREATE INDEX
slo=> create view botched_view as (select content::integer as content_id, * from botched_table where content ~ '^[0-9]{1,9}$');
CREATE VIEW
slo=> explain select * from botched_view where content_id = 1;
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using idx_botched_table on botched_table (cost=0.00..3.72 rows=3 width=32)
Index Cond: ((content)::integer = 1)
Filter: (content ~ '^[0-9]{1,9}$'::text)
(3 rows)
--
greg
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-10 02:50 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-10 06:43 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
@ 2004-09-10 07:02 ` Greg Stark <gsstark@mit.edu>
2004-09-10 13:59 ` Re: Isnumeric function? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Greg Stark @ 2004-09-10 07:02 UTC (permalink / raw)
To: Greg Stark <gsstark@MIT.EDU>; +Cc: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; 'Josh Berkus' <josh@agliodbs.com>; pgsql-sql
Greg Stark <gsstark@MIT.EDU> writes:
> Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
>
> > I created the Index you specified, however it chooses to run a seq scan on
> > the column rather than a Index scan. How can you force it to use that
> > Index..
> >
> > CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> > '^[0-9]{1,9}$';
> >
> > select * from botched_table where content = 200::integer
>
> You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
> to match the clause in the partial index pretty closely.
Well this is weird. I tried to come up with a cleaner way to arrange this than
the view I described before using a function. But postgres isn't using the
partial index when it seems it ought to be available.
When I say it has to match "pretty closely" in this case I think it would have
to match exactly, however in the case of simple range operators postgres knows
how to figure out implications. Ie, "where a>1" should use a partial index
built on "where a>0".
slo=> create table test (a integer);
CREATE TABLE
slo=> create index idx_text on test (a) where a > 0;
CREATE INDEX
slo=> explain select * from test where a > 0;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 0)
(2 rows)
slo=> explain select * from test where a > 1;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 1)
(2 rows)
That's all well and good. But when I tried to make a version of your situation
that used a function I found it doesn't work so well with functional indexes:
slo=> create function test(integer) returns integer as 'select $1' language plpgsql immutable;
CREATE FUNCTION
slo=> create index idx_test_2 on test (test(a)) where test(a) > 0;
CREATE INDEX
slo=> explain select test(a) from test where test(a) > 0;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using idx_test_2 on test (cost=0.00..19.17 rows=334 width=4)
Index Cond: (test(a) > 0)
(2 rows)
slo=> explain select test(a) from test where test(a) > 1;
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..25.84 rows=334 width=4)
Filter: (test(a) > 1)
(2 rows)
I can't figure out why this is happening. I would think it has something to do
with the lack of statistics on functional indexes except a) none of the tables
is analyzed anyways and b) the estimated row count is the same anyways.
--
greg
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-10 02:50 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-10 06:43 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
2004-09-10 07:02 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
@ 2004-09-10 13:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-10 15:33 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2004-09-10 13:59 UTC (permalink / raw)
To: Greg Stark <gsstark@MIT.EDU>; +Cc: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; 'Josh Berkus' <josh@agliodbs.com>; pgsql-sql
Greg Stark <gsstark@MIT.EDU> writes:
> That's all well and good. But when I tried to make a version of your
> situation that used a function I found it doesn't work so well with
> functional indexes:
> ...
> I can't figure out why this is happening.
You're using 7.3 or older?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Isnumeric function?
2004-09-10 02:50 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-10 06:43 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
2004-09-10 07:02 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
2004-09-10 13:59 ` Re: Isnumeric function? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-09-10 15:33 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 29+ messages in thread
From: Greg Stark @ 2004-09-10 15:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Stark <gsstark@MIT.EDU>; Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; 'Josh Berkus' <josh@agliodbs.com>; pgsql-sql
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Greg Stark <gsstark@MIT.EDU> writes:
> > That's all well and good. But when I tried to make a version of your
> > situation that used a function I found it doesn't work so well with
> > functional indexes:
> > ...
> > I can't figure out why this is happening.
>
> You're using 7.3 or older?
7.4.3.
--
greg
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2004-09-10 15:33 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-29 22:46 isnumeric() function? Yudie <yudie@axiontech.com>
2004-04-30 07:14 ` CoL <col@mportal.hu>
2004-04-30 14:11 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-04-30 15:29 ` Yudie <yudie@axiontech.com>
2004-04-30 16:24 ` Frank Bax <fbax@sympatico.ca>
2004-05-01 14:29 Re: isnumeric() function? Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-09-07 05:44 Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-07 06:05 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 16:47 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 17:20 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-08 17:48 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-08 18:11 ` Re: Isnumeric function? Oliver Elphick <olly@lfix.co.uk>
2004-09-09 01:58 ` Re: Isnumeric function? Thomas Swan <tswan@idigx.com>
2004-09-09 14:02 ` Re: Isnumeric function? Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-09-08 23:39 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-08 23:57 ` Re: Isnumeric function? Tom Lane <tgl@sss.pgh.pa.us>
2004-09-09 03:48 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
2004-09-09 18:46 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-09 00:02 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-09 15:01 Re: Isnumeric function? Passynkov, Vadim <Vadim.Passynkov@pathcom.com>
2004-09-10 00:20 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-10 02:21 ` Re: Isnumeric function? Josh Berkus <josh@agliodbs.com>
2004-09-10 04:48 ` Re: Isnumeric function? sad <sad@bankir.ru>
2004-09-10 02:50 Re: Isnumeric function? Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-10 05:39 ` Re: Isnumeric function? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-10 06:43 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
2004-09-10 07:02 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
2004-09-10 13:59 ` Re: Isnumeric function? Tom Lane <tgl@sss.pgh.pa.us>
2004-09-10 15:33 ` Re: Isnumeric function? Greg Stark <gsstark@mit.edu>
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