Thread: Bug or Feature?
Hi all, I'm in the process of switching frrom 7.1.3 to 7.2. The only problem I have so far is the definition of an index. The table contains a timestamp column. In 7.1.3 an index is defined as such: CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops); on 7.2 I have an error message saying that functrional indexes must but made ISCACHABLE. Now should date() be made iscachable, do I have an other way to create trhis index? CAST did'nt wok. TIA Regards, -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery)
Olivier PRENANT <ohp@pyrenet.fr> writes:
> CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops);
> on 7.2 I have an error message saying that functrional indexes must but
> made ISCACHABLE.
See previous discussion of this identical problem. The fact is that
such an index is dangerous, because it depends on the timezone setting.
You might want to make the underlying column be timestamp without time
zone.
regards, tom lane
Hi, Tom Thnaks for you reply; I don't need timezone in this application so it's okay. However, I've been trying to change the table on 7.1.3 to get ready for the swap but there's no timestamp without timezone on 7.1.3; do I really have to edit the dump before reloading, ISTM that it's the best way to generate errors... Regards, On Mon, 4 Mar 2002, Tom Lane wrote: > Olivier PRENANT <ohp@pyrenet.fr> writes: > > CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops); > > on 7.2 I have an error message saying that functrional indexes must but > > made ISCACHABLE. > > See previous discussion of this identical problem. The fact is that > such an index is dangerous, because it depends on the timezone setting. > > You might want to make the underlying column be timestamp without time > zone. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery)
Hi, Tom, Thanks for replying. You were right (of course) I don't need timestamp on this app... However I tried to change the column type on 7.1.3 to ease the swith... AFAIK, there's no timestamp without timezone on 7.1.3; Please tell me there's another way other than editing pg_dump by hand... That would loose time and would call for errors... Regards,On Mon, 4 Mar 2002, Tom Lane wrote: > Olivier PRENANT <ohp@pyrenet.fr> writes: > > CREATE INDEX deb ON xxxx USING btree (date(timestamp coll) date_ops); > > on 7.2 I have an error message saying that functrional indexes must but > > made ISCACHABLE. > > See previous discussion of this identical problem. The fact is that > such an index is dangerous, because it depends on the timezone setting. > > You might want to make the underlying column be timestamp without time > zone. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery)
> You were right (of course) I don't need timestamp on this app...
> However I tried to change the column type on 7.1.3 to ease the
> swith... AFAIK, there's no timestamp without timezone on 7.1.3; Please
> tell me there's another way other than editing pg_dump by hand... That
> would loose time and would call for errors...
Then import the data into your new database and use temporary tables and
drop/create to convert your existing data to the different schema. Then
create the function index you want.
There are probably other ways (as there are other schemas, such as using
DATE rather than TIMESTAMP), but as some point you should choose one and
just do it.
- Thomas