Thread: problem creating rtree index on timestamptz
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, I was trying to create a functional index on a timestamptz column and=20 had the following problem. It seems subraction of an interval from a=20 timestamptz is not immutable. Should this be the case? foo=3D> create table timestamp_tz_test ( start timestamptz primary key ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index=20 "timestamp_tz_test_pkey" for table "timestamp_tz_test" CREATE TABLE =A0 foo=3D> create INDEX idx_timestamptz_rtree on timestamp_tz_test using=20 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=20 error: foo=3D> create table timestamp_test ( start timestamp primary key ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index=20 "timestamp_test_pkey" for table "timestamp_test" CREATE TABLE =A0 foo=3D> create INDEX idx_timestamp_rtree on timestamp_test using rtree=20 ((start - '15 minutes'::INTERVAL)); ERROR: data type timestamp without time zone has no default operator=20 class for access method "rtree" HINT: You must specify an operator class for the index or define a=20 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=3D =3DayoM -----END PGP SIGNATURE-----
Hussein Patni <security@cosbit.com> writes: > It seems subraction of an interval from a > timestamptz is not immutable. Should this be the case? Yes, because the results depend on your local timezone. For example: regression=# set TimeZone TO 'EST5EDT'; SET regression=# select '2005-02-28 17:00-05'::timestamptz - '6 months'::interval; ?column? ------------------------ 2004-08-28 17:00:00-04 (1 row) regression=# set TimeZone TO 'GMT'; SET regression=# select '2005-02-28 17:00-05'::timestamptz - '6 months'::interval; ?column? ------------------------ 2004-08-28 22:00:00+00 (1 row) regression=# select '2004-08-28 17:00:00-04'::timestamptz - '2004-08-28 22:00:00+00'; ?column? ----------- -01:00:00 (1 row) regression=# It's probably true that subtracting an interval expressed in seconds (or equivalent units) is an immutable operation, but we don't have a way to capture that statement in data types, since there's no such restricted interval datatype. You might consider making a function defined like timestamptz - integer (or float) in which the integer is considered as a number of seconds. You could safely mark that as immutable, I think. regards, tom lane