Consequences of Putting Quotes in WHERE Clause for Integer Column in PostgreSQL

performancepostgresqlpostgresql-10query-performancesyntax

I have a table with a column some_column of data type integer.
I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same:

select * from my_table where some_column = 5; -- no quotes

select * from my_table where some_column = '5'; -- quotes added

What are some repercussions of adding quotes in a where clause for an integer column?
Would it have performance effects on a large table of, say, 20 million rows?
(There is an index for some_column.)

Best Answer

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

Related Question