| From: | Hussein Patni <security(at)cosbit(dot)com> |
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org |
| Subject: | problem creating rtree index on timestamptz |
| Date: | 2005-02-28 21:33:12 |
| Message-ID: | a4418a0a181921ecea6c4e5fda6f46d8@cosbit.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello,
I was trying to create a functional index on a timestamptz column and
had the following problem. It seems subraction of an interval from a
timestamptz is not immutable. Should this be the case?
foo=> create table timestamp_tz_test ( start timestamptz primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"timestamp_tz_test_pkey" for table "timestamp_tz_test"
CREATE TABLE
foo=> create INDEX idx_timestamptz_rtree on timestamp_tz_test using
rtree ((start - '15 minutes'::INTERVAL));
ERROR: functions in index expression must be marked IMMUTABLE
I then tried a similar task with just at timestamp column and got this
error:
foo=> create table timestamp_test ( start timestamp primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"timestamp_test_pkey" for table "timestamp_test"
CREATE TABLE
foo=> create INDEX idx_timestamp_rtree on timestamp_test using rtree
((start - '15 minutes'::INTERVAL));
ERROR: data type timestamp without time zone has no default operator
class for access method "rtree"
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
Should there be a default for this?
Thanks in advance,
- --hussein
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)
iD8DBQFCI44ZI2IgiXMhqe4RAnHvAKC9o2xHmfdQ0MuYJ8XWK3v9uxf5IgCgnCuk
tD2jJUIWaGlPtbiBcByE1HU=
=ayoM
-----END PGP SIGNATURE-----
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2005-02-28 22:13:05 | Re: problem creating rtree index on timestamptz |
| Previous Message | Daniel Agut | 2005-02-28 12:38:13 | Errors using Postgres 7.4.6 |