Question 291506 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Query Not Returning Rows for Current Month

PostgreSQL version: 14.10

I’m facing an issue with a PostgreSQL SQL query that aims to retrieve records from the partner_online_time table for the current month. The query is as follows:

SELECT *
FROM partner_online_time
WHERE TRIM(month) = TO_CHAR(CURRENT_DATE, 'Month');

Despite having confirmed that TO_CHAR(CURRENT_DATE, ‘Month’) correctly returns the expected month name (November in my case), this query doesn’t return any rows. I have records in the partner_online_time table for November.

Here is a sample of the records in partner_online_time for November:

id  | restaurant_drn |  month   | year | online_mins 
----+----------------+----------+------+------------
  8 | Restaurant2    | November | 2023 |         180 
  9 | Restaurant3    | November | 2023 |         210

What could be the issue with my PostgreSQL query, and how can I modify it to retrieve the desired records?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The problem was in the comparison of month names, where TO_CHAR(CURRENT_DATE, 'Month') returned a value with trailing spaces ('November '). To resolve this, the query was adjusted to apply LOWER and TRIM functions to both sides of the comparison, ensuring consistent case and removing any leading or trailing spaces. The corrected query is as follows:

    SELECT * FROM partner_online_time WHERE LOWER(TRIM(month)) = LOWER(TRIM(TO_CHAR(CURRENT_DATE, 'Month')));

    Appreciations to @adrianklaver for pointing out the formatting issue!


  2. 0

    By default, PostgreSQL is case-sensitive when compared. So shouldn’t be the query like this

    WHERE LOWER(TRIM(month)) = LOWER(TO_CHAR(CURRENT_DATE, 'Month'));

    Here I believe that you don’t have any trailing/leading spaces in your column

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search