Postgres 10

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?.

-- interval typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < interval '6 days'; -- works

?column?

f

--untyped string literal
SELECT current_timestamp - timestamp '2018-05-04 18:40' < '6 days'; -- works

?column?

f

-- text typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < text '6 days'; -- fails!
ERROR:  operator does not exist: interval < text
LINE 2: ... current_timestamp - timestamp '2018-05-04 18:40' < text '6 ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

-- text typed value, with explicit cast
SELECT current_timestamp - timestamp '2018-05-04 18:40' < ('6 days'::text::interval); -- works

?column?

f

PREPARE fooplan_typed_interval(interval) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_interval('6 days');

?column?

f

PREPARE fooplan_untyped AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_untyped('6 days');

?column?

f

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_text('6 days');  -- only this one fails!
ERROR:  operator does not exist: interval < text
LINE 2: ...ELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;
                                                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1::interval;

EXECUTE fooplan_typed_text('6 days');

?column?

f