Yellowfin Filters and Postgres ILIKE

Hi Will,

Thanks for reaching out. Have you already confirmed using "Contains" doesn't work in your use case? From my understanding PostgreSQL is case-insensitive by default, and looking at how the tables are created by yellowfin installer, it looks like it does it in a case insensitive way:

CREATE TABLE public.person
(
    ipperson integer,
    lastname character varying(256) COLLATE pg_catalog."default",
    firstname character varying(256) COLLATE pg_catalog."default",
    middleinitial character varying(40) COLLATE pg_catalog."default",
...

Also, just so I have a deeper understanding of this scenario, are you unable to use ILIKE in a Freehand SQL statement in your use case? If this is true, can you please explain why this is not possible so I can submit this along with the enhancement request? The reason I ask this is because I don't believe any of our 'Configure Filter' options contain RDBMS-specific options by design, as for example, a PostgreSQL-specific option such as ILIKE being available would likely be confusing a majority of our users', considering it wouldn't work for other more common RDBMS'. I believe that RDBMS-specific options are intended to be used in Freehand SQL statements instead.

Regardless, please provide feedback on my queries above then we can consider next steps.

Thanks,

Mike

Hello Mike!

Postgres treats all strings as case sensitive unless you explicitly ask it otherwise either by using the citext extension (not viable for us), ILIKE instead of LIKE, or just using lowers. We can very likely add lower() behavior and handle this behavior by running lower in a new column in our queries then comparing against that. Our use case is taking user input and passing it to the javascript api so there may be some things we can do on our end.

Feel free to close this ticket since the parity will not work out 😊

Thanks for responding!

Will

Hi Will,

Thanks for getting back to me. I'll close this off for now then, as requested, but if you wind up having any more questions or concerns on this, if you respond, the ticket will reopen and I'll be happy to assist further.

Regards,

Mike