Apparantly creating a function index does not work if there is a single
quote in the function expression? Example:
CREATE TABLE A (
x INTEGER
, y DATE
)
;
INSERT INTO A VALUES( 1, CURRENT_DATE );
INSERT INTO A VALUES( 2, CURRENT_DATE );
INSERT INTO A VALUES( 3, CURRENT_DATE );
INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('1 day' AS INTERVAL) );
--INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('2 day' AS INTERVAL) );
--INSERT INTO A VALUES( 4, CURRENT_DATE - CAST('3 day' AS INTERVAL) );
CREATE INDEX indx1 ON A( DATE_TRUNC('day', y) );
SELECT * FROM A WHERE y < CURRENT_DATE;
DROP INDEX indx1;
DROP TABLE A;
Please advise. Thanks.
-- Michael