Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> 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 happenin
Greg Stark <[EMAIL PROTECTED]> 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?
Greg Stark <[EMAIL PROTECTED]> writes:
> Theo Galanakis <[EMAIL PROTECTED]> 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 b
Theo Galanakis <[EMAIL PROTECTED]> 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}$
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:[EMAIL PROTECTED]
> Sent: Friday, 10 September 2004 4:46 AM
>
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.
---(end of broadcast)-
Title: RE: [SQL] Isnumeric function?
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
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 fro
Title: RE: [SQL] Isnumeric function?
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-
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 th
To: Thomas Swan; [EMAIL PROTECTED]
Cc: Josh Berkus; Theo Galanakis; [EMAIL PROTECTED]
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
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. ;-)
Theo Galanakis <[EMAIL PROTECTED]> 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 a
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 brut
Title: RE: [SQL] Isnumeric function?
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
Theo Galanakis <[EMAIL PROTECTED]> 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 sear
Title: RE: [SQL] Isnumeric function?
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" varc
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 ;-)
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
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 lead
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
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 check
Title: Isnumeric function?
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
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.
---(end of broadcast)---
TIP 9: the planner
> - Original Message -
> From: "Jeff Eckermann" <[EMAIL PROTECTED]>
> To: "Yudie" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Friday, April 30, 2004 9:11 AM
> Subject: Re: [SQL] isnumeric() function?
>
>
> --- Yudie <[
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 be
Great the function works, but what does it means?
SELECT $1 ~ ''^[0-9]+$''
Yudie
- Original Message -
From: "Jeff Eckermann" <[EMAIL PROTECTED]>
To: "Yudie" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, Apri
--- Yudie <[EMAIL PROTECTED]> 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]+$''
' LANG
What is isnumeric function in postgresql?
I'm using psql version 7.2.2
thanks
Yudie
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
29 matches
Mail list logo
