Re: [GENERAL] Create Index on Date portion of timestamp

2006-10-13 Thread A. Kretschmer 
am  Thu, dem 12.10.2006, um 18:40:22 -0700 mailte Niederland folgendes:
> I am using postgresql 8.1.4.
> 
> Is there anyway to create an index equivalent to:
> CREATE INDEX i1 ON t1 USING btree  (ts::Date);

CREATE INDEX i1 ON t1 USING BTREE (to_char(ts, 'dd-mm-' ));

*untested*


Please, let me know, if this okay.


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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Create Index on Date portion of timestamp

2006-10-13 Thread Tom Lane 
"Niederland" <[EMAIL PROTECTED]> writes:
> I am using postgresql 8.1.4.
> Is there anyway to create an index equivalent to:
> CREATE INDEX i1 ON t1 USING btree  (ts::Date);

You're short some parentheses:

CREATE INDEX i1 ON t1 USING btree  ((ts::Date));

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Create Index on Date portion of timestamp

2006-10-13 Thread Andrew Sullivan 
On Thu, Oct 12, 2006 at 06:40:22PM -0700, Niederland wrote:
> I am using postgresql 8.1.4.
> 
> Is there anyway to create an index equivalent to:
> CREATE INDEX i1 ON t1 USING btree  (ts::Date);
> 
> So that indexes are used for queries when the field is cast to a date.

I didn't try it, but you ought to be able to create a functional
index on the to_date() of the column.  I don't know if that will
solve your cast issue, but you could rewrite the CAST into the
to_date form to get around that.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Create Index on Date portion of timestamp

2006-10-13 Thread Niederland 
I am using postgresql 8.1.4.

Is there anyway to create an index equivalent to:
CREATE INDEX i1 ON t1 USING btree  (ts::Date);

So that indexes are used for queries when the field is cast to a date.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/