Thread: [BUGS] BUG #14693: create materialized view forces btrim
The following bug has been logged on the website: Bug reference: 14693 Logged by: Ian Boardman Email address: isb0459@gmail.com PostgreSQL version: 9.4.9 Operating system: Fedora 23 Description: 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? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
isb0459@gmail.com writes: > 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. trim() *is* btrim(). Whatever's going on here, you've leapt to an incorrect conclusion about the cause. How about showing us a self-contained example of your problem? https://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
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.
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:
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.
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.
So does "SELECT 'a non empty value';"...I guess if you're happy, great, but your solution and the original problem look nothing alike.
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(?!)
I don't know why btrim is the canonical spelling of the trim function but, like Tom said, it is. The choice of btrim or trim will not influence the end result of the query in any way (though I don't think btrim accepts the "from" keyword that trim does).
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I don't know why btrim is the canonical spelling of the trim function but,
> like Tom said, it is. The choice of btrim or trim will not influence the
> end result of the query in any way (though I don't think btrim accepts the
> "from" keyword that trim does).
FWIW, btrim is just a plain function. Our grammar maps the weird syntax
defined by the SQL standard for TRIM() into a call of btrim, ltrim, or
rtrim depending on what keywords appear. The functionality is the same
whether you go through that syntax or just use btrim directly.
I'm inclined to suspect that the OP's real problem has to do with
confusion between empty strings and nulls, but it's hard to be sure
with no concrete example.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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.
Given your subsequent descriptions I'm confused as to your claim that you have a working SQL query when not used as part of a create materialized view. You should have found that running the same simple select by itself was always returning empty foo strings instead of bar.
David J.