I am looking to build a filter component where my search comes like b_cycle_type = '1st Day Of The Month'

and in database b_cycle_type is stored as -1,0,1,2,3,4,5

How can i prepare postgres statement

I am trying

SELECT "customers".* FROM "customers" WHERE (CASE customers.b_cycle_type
                            WHEN -1 THEN 'Last day of the month'
                            WHEN 0 THEN 'Align with first'
                            ELSE
                              to_char(customers.b_cycle_type, '99th') || ' Day Of The Month'
                            END = '27th Day Of The Month')

Its not returning me any result.

CodePudding user response：

This part to_char(customers.b_cycle_type, '99th') actually results in ' 27th' so to combat this I would use the TRIM function.

CREATE TABLE customers(
   id SERIAL PRIMARY KEY, 
   b_cycle_type INT);
INSERT INTO customers(b_cycle_type)
SELECT * FROM generate_series(-1,30);

SELECT "customers".*
FROM "customers"
WHERE 
  CASE b_cycle_type
   WHEN -1 THEN 'Last day of the month'
   WHEN 0 THEN 'Align with first'
   ELSE
     TRIM(to_char(b_cycle_type, '99th')) || ' Day Of The Month'
 END = '27th Day Of The Month'

Example

to_char Docs(*6th from the bottom)

CodePudding user response：

SELECT "customers".* 
FROM "customers" 
WHERE (CASE b_cycle_type
         WHEN -1 THEN 'Last day of the month'
         WHEN 0 THEN 'Align with first'
         ELSE 
           CASE 
              WHEN "customers".b_cycle_type BETWEEN -1 AND 31 
              THEN trim(to_char("customers".b_cycle_type,'99th')||' Day Of The Month')
           END
      END = '27th Day Of The Month');

But:

1. If b_cycle_type column is a date, you should define the column as a date type, not a numerical type. It would enables you to do simply where extract('day' from b_cycle_type) = 27. It'll also take care of validating all data anyone tries to insert into the table.
2. If for whatever reason you have to have this as a day offset, you should make it a smallint or even decimal(2,0), not any other numeric. Also, save the actual date it corresponds to, as a separate column, in order to be able to easily account for months with different lengths, as well as leap years when February is longer.
3. If you can't alter the "customers" table structure, whenever you deal with the data from the table, you should make sure b_cycle_type is between -1 and 31, and within possible number of days for a given month in a given year.