Both do the same and you won't be able to measure any difference in performance.
Details
This is a string literal or string constant: '5'
The manual:
A string constant in SQL is an arbitrary sequence of characters
bounded by single quotes ('
), for example 'This is a string'
. To
include a single-quote character within a string constant, write two
adjacent single quotes, e.g., 'Dianne''s horse'
. Note that this is not
the same as a double-quote character ("
).
If there is no context from which a type can be derived, a string literal is initially assumed to be type text
. (Not the case in your example.)
The manual once more:
The explicit type cast can be omitted if there is no ambiguity as to
the type the constant must be (for example, when it is assigned
directly to a table column), in which case it is automatically coerced.
This is a numeric literal or numeric constant: 5
The manual:
A numeric constant that contains neither a decimal point nor an
exponent is initially presumed to be type integer
if its value fits in
type integer
(32 bits); otherwise it is presumed to be type bigint
if
its value fits in type bigint
(64 bits); otherwise it is taken to be
type numeric
. Constants that contain decimal points and/or exponents
are always initially presumed to be type numeric
.
So a numeric literal starts out with a specific type. It may then be cast to a different type as the context requires - if such a cast is defined. That's a subtle, but important difference - which makes no effective difference in your case, since 5
is initially integer
, which is exactly the type it needs to be.
But it matters in other cases. Try this:
CREATE TEMP TABLE tbl1 (t int);
SELECT * FROM tbl1 where t = '0'; -- works!
SELECT * FROM tbl1 where t = int '0'; -- works!
SELECT * FROM tbl1 where t = int2 '0'; -- works!
SELECT * FROM tbl1 where t = 0; -- works!
CREATE TEMP TABLE tbl2 (t text);
SELECT * FROM tbl2 where t = '0'; -- works
SELECT * FROM tbl2 where t = text '0'; -- works
SELECT * FROM tbl2 where t = varchar '0'; -- works
SELECT * FROM tbl2 where t = 0; -- fails !!!
ERROR: operator does not exist: text = integer
Because the numeric literal starts out as integer
and there is no assignment cast defined for integer
--> text
. (Any type can be cast to text
with an explicit cast (0::text
) but that cast is not assumed here.)