Re: BUG #14693: create materialized view forces btrim

On Tue, Jun 6, 2017 at 1:25 PM, <isb0459(at)gmail(dot)com> wrote:

> I am trying to create a materialized view. I have a working SQL query that
> uses this expression: coalesce(trim(foo), bar). When I use that in the
> definition for create materialized view, Postgres force replaces the trim()
> call into a btrim() and breaks the query result. In my specific case, all
> the values become empty instead of yielding either the trimmed "foo" or the
> "bar" fields if trimmed "foo" is empty. Is there a way to make Postgres use
> the function trim as I require?
>

​COALESCE returns the first non-null value. The empty string is non-null
and so it will be returned.

try:

COALESCE(NULLIF(trim(foo), ''), bar);

David J.
​