Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE foo (straw text);

CREATE TABLE

-- standard SQL notation works, of course
CREATE INDEX ON foo (CAST(straw AS int));

CREATE INDEX

-- short notation with the cast operatror is disallowed
CREATE INDEX ON foo (straw::int);

ERROR:  syntax error at or near "::"
LINE 2: CREATE INDEX ON foo (straw::int);
                                  ^

-- but it works with parentheses:
CREATE INDEX ON foo ((straw::int));

CREATE INDEX

-- note that either notation in a query qualifies for using the index with eithe notation
SELECT * FROM foo WHERE straw::int = 123;

straw

SELECT 0