CODEWITHSUNDEEP
postgresqldate
TeckFudge
TeckFudge

Reputation: 169

Comparing day of week to String literal name for day of week returns false

I am comparing the day of week values to their string literal equivalents, but am consistently getting false. I have a relatively straightforward string DoW to 'monday' comparison setup in SQL Fiddle, however, only the DoW to int compare works. I plan to use that going forward, but I am trying to understand what is failing.

http://sqlfiddle.com/#!17/2d52d/29

day1 like day2 AS like_compare,       //Expect true,  get false
day1 = day2 AS equal_compare,         //Expect false, get false
day1::text like day2 AS text_compare, //Expect true,  get false
EXTRACT(DOW FROM '2019-08-05') = 1 AS value_compare //Expect true, get true

I am trying to understand what is happening that is causing PostgreSQL to act this way. As you can see in the fiddle, none of the relevant string literals have an 'unknown' type. I accept that the correct way to compare days of weeks is to use the 'value' compare, however, that doesn't explain what is happening with the 'like' and 'text' compares.

Upvotes: 0

Views: 50

Answers (1)

Belayer
Belayer

Reputation: 14934

The difference stems from extract and to_char handling Day/DOW requests slightly differently. See EXTRACT DOW and TO_CHAR. In this particular case try trim on day1.

WITH mySelect AS 
(  SELECT
      to_char(day1, 'day') AS day1,
      day2 
   FROM
      myTable
)
SELECT
   *,
   pg_typeof(day1)::text like pg_typeof(day2)::text AS types_match,   
   trim(day1) like day2 AS like_compare,
     trim(day1) = day2 AS equal_compare,
     trim(day1::text) like day2 AS text_compare,
     EXTRACT(DOW FROM( SELECT day1  FROM myTable)) = 1 AS value_compare,
     CAST('monday' AS text) like CAST('monday' AS text) AS sanity_compare 
FROM
   mySelect;

Upvotes: 1

Related Questions