Re: [BUGS] BUG #14693: create materialized view forces btrim

Поиск
Список
Период
Сортировка
От Ian Boardman
Тема Re: [BUGS] BUG #14693: create materialized view forces btrim
Дата
Msg-id CA+i3Ta0FmpW-bkhg3gbyGAsskb+ZbN7Af0Ua=WnwWn6PVHezMg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14693: create materialized view forces btrim  ("David G. Johnston" <david.g.johnston@gmail.com>)
Ответы Re: [BUGS] BUG #14693: create materialized view forces btrim  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
Thanks very much folks. For your entertainment, this seems to be working. I changed the expression to  trim(from foo) || bar,  in my select statement, and reliably got a non-empty result.  I committed this via "create materialized view", and inspected what Postgres did with it (\dSm+). It still is stored as btrim(foo) || bar; and yet, it works correctly(?!)

On Tue, Jun 6, 2017 at 4:51 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jun 6, 2017 at 1:25 PM, <isb0459@gmail.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.
​

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: [BUGS] BUG #14693: create materialized view forces btrim
Следующее
От: Mike Palmiotto
Дата:
Сообщение: Re: [BUGS] BUG #14682: row level security not work with partitioned table