[SQL]
How do you get FTP access to http://developer.postgresql.org/docs/pgsql/contrib/ ftp://developer.postgresql.org/docs/pgsql/contrib/ Asks for a username and password, tried annonymous. Theo __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, 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.
Re: [SQL] Query Plan
Theo Galanakis wrote: Have a question regarding when a Query Plan uses an Index. I have a basic statement This query uses the Index Scan: explain analyse select * from tablea where columna_id < 57 This query uses Seq Scan: explain analyse select * from tablea where columna_id < 58 There are about 5000 records in this table, and columna_id is the primary key, which is Indexed... And recently recreated. OK - what does explain analyse say? Each line will have two parts, the expected costs/number of rows and the actual time/number of rows. If PG is expecting say 50 rows in the first query but 150 in the second then it might decide a sequential scan is quicker. Post the outputs of explain analyse and we can compare them. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED])
[SQL] Datetime conversion in WHERE clause
Hello, I'm converting a varchar to a date in a search routine which looks like: -- CREATE FUNCTION public.search_data(varchar) RETURNS SETOF foo1 AS ' SELECT DISTINCT foo1.* FROM foo1 LEFT JOIN foo2 ON foo2.fk = foo1.pk WHERE lower(foo1.text_data1) LIKE lower($1) OR lower(foo1.text_data2) LIKE lower($1) OR foo1.date_data = $1::text:date OR lower(foo2.text_data3) LIKE lower($1) OR lower(foo2.text_data4) LIKE lower($1) ' LANGUAGE 'sql' VOLATILE; -- The problem with the ::text::date conversion is that the whole function fails if the search string parameter cannot be converted into a valid date, like 'test%', for example, which is valid for the other text fields. Is there a way to force the date conversion to fail sliently, and simply return a null in case the parameter is not a valid date? Thanks Philippe Lang ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Isnumeric function?
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:[EMAIL PROTECTED]
Sent: Thursday, September 09, 2004 10:02 AM
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 "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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED])
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] aggregate function stddev
Kemin, > Just noticed that the postgres stddev is the stddev_sample formula. > There are two different ways to calculate this value. > Their difference is very small with large samle size. It would be nice > to distinguish the two different versions. Note sent to PGSQL-DOCS. > I also noticed that oracle has stddev_sample and stddev_population. > This is just a wish list. Were you aware that in PostgreSQL you can write your own aggregates? It's relatively easy to do. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Datetime conversion in WHERE clause
Philippe, > Is there a way to force the date conversion to fail sliently, and simply > return a null in case the parameter is not a valid date? You'd need to write a custom function, using an external language that allows you to test for valid date values. I prefer using PL/perlU with Date::Manip, but use what you like. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Isnumeric function?
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-
From: Jeff Eckermann [mailto:[EMAIL PROTECTED]]
Sent: Friday, 10 September 2004 12:02 AM
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 "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 <[EMAIL PROTECTED]> 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 addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, 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.
Re: [SQL] Isnumeric function?
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Isnumeric function?
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 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:[EMAIL PROTECTED]]
Sent: Friday, 10 September 2004 4:46 AM
To: Theo Galanakis; [EMAIL PROTECTED]
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 addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, 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.
Re: [SQL] Isnumeric function?
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)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Isnumeric function?
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:[EMAIL PROTECTED]
> Sent: Friday, 10 September 2004 4:46 AM
> To: Theo Galanakis; [EMAIL PROTECTED]
> 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Isnumeric function?
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}$';
>
> 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
