2

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

5
  • 1
    You can compare the 2 execution plans. May 24, 2019 at 14:38
  • They appear to be the same.
    – Anthony
    May 24, 2019 at 14:44
  • 3
    The question is, why do you want to supply a character value if you know the target column is integer?
    – mustaccio
    May 24, 2019 at 15:56
  • @mustaccio: That's not the question at all. The OP didn't say he wanted to do that. Rather he wants to know about possible effects. The question is pretty clear and valid to me. May 24, 2019 at 16:39
  • @ErwinBrandstetter Sorry, I have to disagree: it has "why" in the beginning and a question mark at the end, so it is a question. You may not be able to answer it, and it's fine. Note I have said nothing about the clarity or validity of the original question.
    – mustaccio
    May 24, 2019 at 16:56

1 Answer 1

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.