Обсуждение: Bug #620: create index ... (date_part('year', d)) fails
Ernst Molitor (molitor@uni-bonn.de) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description create index ... (date_part('year', d)) fails Long Description IMHO, creation of an index on a part of a date field should work, but it fails due to the definition of "create index ..." in gram.y. Maybe I'm wrong, but I feel that for large tables which will have to be analyzed per year, an index of this kind could bean advantage. Sample Code -- the table create table test (d date); -- partial indices: create index d_year on test (date_part('year', d)); -- fails with: ERROR: parser: parse error at or near "'" create index d_year on test (extract(year from d)); -- fails with: ERROR: parser: parse error at or near "(" No file was uploaded with this report
sszabo@bigpanda.com On Fri, 15 Mar 2002 pgsql-bugs@postgresql.org wrote: > Ernst Molitor (molitor@uni-bonn.de) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > create index ... (date_part('year', d)) fails > > Long Description > IMHO, creation of an index on a part of a date field should work, > but it fails due to the definition of "create index ..." in gram.y. > > Maybe I'm wrong, but I feel that for large tables which will have to > be analyzed per year, an index of this kind could be an advantage. You need to make a function that is marked iscachable that returns date_part('year', <argument>) and use that. IIRC, functional indices require that all of the arguments be column references.