CODEWITHSUNDEEP
postgresqltextcharstring-comparison
gotqn
gotqn

Reputation: 43636

Why text value is not equal to char value?

I am looking through a book about PostgreSQL and find the following example:

SELECT
        'a '::VARCHAR(2) = 'a '::TEXT AS "Text and varchar",
        'a '::CHAR(2) = 'a '::TEXT AS "Char and text",
        'a '::CHAR(2) = 'a '::VARCHAR(2) AS "Char and varchar";

which yields:

enter image description here

which is strange and in the book is said:

The preceding example shows that 'a '::CHAR(2) equals 'a '::VARCHAR(2), but both have different lengths, which isn't logical. Also, it shows that 'a'::CHAR(2) isn't equal to 'a '::text. Finally, 'a '::VARCHAR(2) equals 'a'::text. The preceding example causes confusion because if variable a is equal to b, and b is equal to c, a should be equal to c according to mathematics.

But there is no explanation why? Is there something about how data is stored or maybe this is some legacy behavior of the CHAR type?

Upvotes: 1

Views: 567

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

The behavior you are seeing appears to be explained by that Postgres is doing some casting behind the scenes. Consider the following slightly modified version of your query:

SELECT
    'a '::VARCHAR(2) = 'a '::TEXT AS "Text and varchar",
    'a '::CHAR(2) = CAST('a '::TEXT AS CHAR(2)) AS "Char and text",
    'a '::CHAR(2) = 'a '::VARCHAR(2) AS "Char and varchar";

This returns True for all three comparisons. Apparently Postgres is casting both sides of the second comparison to text, and a[ ] ([ ] indicates a space) is not the same thing coming from a CHAR(2) and from text.

Recall that in order to do an A = B comparison in a SQL database, both types of A and B have to be the same. If not, even if it appears that the equality comparison be working on its own, under the hood most likely there is an implicit cast happening.

Upvotes: 2

Related Questions