Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- interval typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < interval '6 days'; -- works

?column?

f

SELECT 1

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

?column?

f

SELECT 1

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

SELECT 1

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

EXECUTE fooplan_typed_interval('6 days');

PREPARE

?column?

f

SELECT 1

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

EXECUTE fooplan_untyped('6 days');

PREPARE

?column?

f

SELECT 1

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 types. 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');

PREPARE

?column?

f

SELECT 1