[SQL] Full Text Index Stats

[Ryan Hansen]

Greetings,

 

I couldn't find anything about this in the documentation or the mailing list
archives, so forgive me if this has already been addressed.  

 

I'm trying to determine if the full text indexing has any built-in
capability for providing information about word occurrence, i.e. the number
of times a given word is used.  I read about the ts_stat function, but
either I don't understand the syntax well enough or I don't think this is
what I'm looking for.  I know it can be used for determining the most used
words in the index (although this is very slow), but I want to be able to
get the number of occurrences on a given word.Is that kind of thing
built in somewhere or do I need to write it myself?

 

Thanks!

[SQL] Date Index

[Ryan Hansen]

Hey all,

 

I'm apparently too lazy to figure this out on my own so maybe one of you can
just make it easy on me. J  

 

I want to index a timestamp field but I only want the index to include the
-mm-dd portion of the date, not the time.  I figure this would be where
the "expression" portion of the CREATE INDEX syntax would come in, but I'm
not sure I understand what the syntax would be for this.

 

Any suggestions?

 

Thanks!

Re: [SQL] Date Index

[Ryan Hansen]

Incidentally,  extract(date from ts) doesn't work on my install of 8.3
(standard Ubuntu Hardy apt install).  I get a "timestamp units "date" not
recognized" error when I try it.  The field I'm trying to create it on is
"timestamp without time zone".

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of A. Kretschmer
Sent: Friday, October 31, 2008 1:49 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Date Index

am  Thu, dem 30.10.2008, um 14:49:16 -0600 mailte Ryan Hansen folgendes:
> Hey all,
> 
>  
> 
> I?m apparently too lazy to figure this out on my own so maybe one of you
can
> just make it easy on me. J 
> 
>  
> 
> I want to index a timestamp field but I only want the index to include the
> -mm-dd portion of the date, not the time.  I figure this would be
where the
> ?expression? portion of the CREATE INDEX syntax would come in, but I?m not
sure
> I understand what the syntax would be for this.
> 
>  
> 
> Any suggestions?

Sure.

You can create an index based on a function, but only if the function is
immutable:

test=# create table foo (ts timestamptz);
CREATE TABLE
test=*# create index idx_foo on foo(extract(date from ts));
ERROR:  functions in index expression must be marked IMMUTABLE


To solve this problem specify the timezone:

For the same table as above:
test=*# create index idx_foo on foo(extract(date from ts at time zone
'cet'));
CREATE INDEX


If you have a timestamp whitout timezone it is much easier:
test=# create table foo (ts timestamp);
CREATE TABLE
test=*# create index idx_foo on foo(extract(date from ts));
CREATE INDEX





Hope thats help, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql