Re: [SQL] indexing and LIKE
Patrik Kudo <[EMAIL PROTECTED]> writes: > Thanks for your respons, but I'm afraid it didn't help. I've succeeded > with indexing my table using functional indices, but the problem is that > the index I create won't work with my query. Works for me: test71=# create table person (last_name text); CREATE test71=# create index person_lower_lname_idx on person (lower(last_name)); CREATE test71=# explain select * from person where lower(last_name) like 'kud%'; NOTICE: QUERY PLAN: Index Scan using person_lower_lname_idx on person (cost=0.00..8.16 rows=10 width=12) EXPLAIN The difference is probably a locale problem: if you aren't in C locale then the index LIKE optimization is disabled because it doesn't work reliably. See the list archives for more info. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] DROP VIEWS
Hi, I am trying to drop a view 'activity_IP' (notice that last two letters are capitalized), but get an exception saying: ERROR: view "activity_ip" does not exist Here, the last two letters are lower-case. There is an entry in pg_views table for view 'activity_IP', but not for 'activity_ip'. How can I drop this view? Thanks, Oleg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] select 5/2???
Which postgres version? in PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96 : db=> select -5./2.; ?column? -- -2.5 (1 row) CoL BELLON Michel wrote: > The good select is > > SELECT 5./2. > > BUT > > select -5./2. > +--+ > | ?column? | > +--+ > | 2.5 | not -2.5 > +--+ > > > > Michel BELLON > LCIE - Informatique appliquée > 33 (0)1 40 95 60 35 > > >>-Message d'origine- >>De: guard [SMTP:[EMAIL PROTECTED]] >>Date: mercredi 3 octobre 2001 19:05 >>À:[EMAIL PROTECTED] >>Objet:select 5/2??? >> >>dear all >> >>I run select 5/2 = 2 >>who to get "2.5" >> >>thanks >> >> >>-- >> >> >> >> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] MEDIAN as custom aggregate?
Folks, Hey, anybody have a custom aggregate for median calucation? I'm doing this through a PL/pgSQL function, and a custom aggregate would probably be faster. For those whose stats terminology is rusty, the "median" is the "middle" value in a distribution. For example, if we had the following data: Table ages person age Jim 21 Rusty 24 Carol 37 Bob 62 Leah78 Our Median would be Carol's age, 37. This is a different figure from the Mean, or Average, which is 44.4. Using the combination of the Mean and the Median you can do all kinds of interesting statistical analysis. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL CONSTRAINTS - Constraining time values from two attributes on
> I was looking for a solution on how to write a constraint into a ' create > table ' expression that would ensure that one ' TIME ' attribute value > called arrival_time (declared as TIME) is allways constrained to have a > value that ensures it is allways after another attribute value called > departure_time (declared as TIME). > Any know how this constraint would be written?? > CREATE TABLE tablename ( arrival_time timestamp, departure_time timestamp CHECK (arrival_time < departure_time) ); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] indexing and LIKE
On Fri, 12 Oct 2001, Patrik Kudo wrote: > kudo=# select version(); >version > -- > PostgreSQL 7.1.3 on i386--freebsd4.3, compiled by GCC 2.95.3 > (1 row) > > kudo=# create index person_lower_lname_idx on person (lower(last_name)); > CREATE > kudo=# vacuum analyze person; > VACUUM > kudo=# explain select userid, first_name, last_name from person where >lower(last_name) like 'kud%'; > NOTICE: QUERY PLAN: > > Seq Scan on person (cost=0.00..217.44 rows=70 width=36) > > EXPLAIN > kudo=# explain select userid, first_name, last_name from person where >lower(last_name) = 'kudo'; > NOTICE: QUERY PLAN: > > Index Scan using person_lower_lname_idx on person (cost=0.00..57.82 rows=70 >width=36) > > As you can see, the functional index is working fine when using the > "normal" = operator. However, it is not used when using the "like" > operator, which I need. I understand that a pattern-matched query probably > can't be made as effective as a query with =, but I think it, at least > theoretically, should be possible to use a btree-index to find matches in > the first query above. > > Am I totaly wrong here? What is possible/impossible with Postgres? Are you running with locale support turned on, and if so what locale? IIRC, if it's not C locale Postgres won't use the index. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] MEDIAN as custom aggregate?
Can't you do something like select age from ages order by age limit 1 offset (select count(*) from ages)/2; except you can't nest the select so you'll have to use a variable to hold it... Make sure it does the right thing when there is an odd number of rows. I don't understand why you want the median and not some parameters of your assumed distribution (mean and variance, for example) but each to his own... Allan. Josh Berkus wrote: > Folks, > > Hey, anybody have a custom aggregate for median calucation? I'm doing > this through a PL/pgSQL function, and a custom aggregate would probably > be faster. > > For those whose stats terminology is rusty, the "median" is the "middle" > value in a distribution. For example, if we had the following data: > > Table ages > person age > Jim 21 > Rusty 24 > Carol 37 > Bob 62 > Leah78 > > Our Median would be Carol's age, 37. This is a different figure from > the Mean, or Average, which is 44.4. Using the combination of the Mean > and the Median you can do all kinds of interesting statistical analysis. > > -Josh Berkus > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] MEDIAN as custom aggregate?
Allan,
> Can't you do something like
>
> select age from ages order by age limit 1 offset (select count(*)
> from ages)/2;
>
> except you can't nest the select so you'll have to use a variable to
> hold it...
>
> Make sure it does the right thing when there is an odd number of
> rows.
Duuuh! I feel like a real idiot now.
The query I'll use is this:
SELECT site, COUNT(pageviews) as count_views, AVG(pageviews) AS
mean_views, median_views
FROM sites,
(SELECT pageviews as median_view
FROM pageviews
LIMIT 1 OFFSET middlerec('pageviews')) med
GROUP BY site, median_views;
Where middlerec is a custom function that counts the records and returns
the middle one.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. 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])
[SQL] Indexes
Can someone shed some light on as to how the indexes actually work? I want to index the results of a function, the function does a whole lot of different selects on different tables. Will the database know when to update the index. If there already is some documentation on this (besides the source...) I would be glad to know. Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] GROUPING
It's been a while since I used postgresql but today I have converted one of my web apps but with one small problem. I goto do a group as designed and executed in mysql and I get told that this and this must be part of the aggreate etc I am puzzled and wonder if someone could bring me up to stratch with grouping in postgresql this is my current sql: SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; Result: Attribute telemetry.rpvuid must be GROUPed or used in an aggregate function Oh then if I include rpvuid I get you must include this field and on it goes. Thanks... Timothy J Hitchens [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] DROP VIEWS
You'll need to double quote the name, "activity_IP" On Fri, 12 Oct 2001, Oleg Lebedev wrote: > Hi, > I am trying to drop a view 'activity_IP' (notice that last two letters > are capitalized), but get an exception saying: > ERROR: view "activity_ip" does not exist > Here, the last two letters are lower-case. > There is an entry in pg_views table for view 'activity_IP', but not for > 'activity_ip'. > How can I drop this view? > Thanks, > > Oleg > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Indexes
On Fri, 12 Oct 2001, Aasmund Midttun Godal wrote: > Can someone shed some light on as to how the indexes actually work? > > I want to index the results of a function, the function does a whole > lot of different selects on different tables. Will the database know > when to update the index. AFAIK No. The functions for functional indexes probably should only depend upon the arguments and no outside data. Anything else sounds like a recipe for broken indexes. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
