Re: Reg: SQL Query for Postgres 8.4.3

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I think the OP is probably running a version that doesn't include
> the Jan 7 commit, which was effectively undone by the Jan 25
> commit for CVS HEAD.

It sure looks like it.

> It looks like this was intentional based on spec behavior
> of overlay(), but should we consider maintaining the historical
> behavior instead?

I know I read through the spec (several versions of it) related to
this issue when I reviewed the patch, and if memory serves the 9.0
behavior is what the spec requires. Obviously that's a behavior
change, so it can't be back-patched. I'm inclined to think the
previous behavior was pretty marginal, and there is certainly a
workaround -- omit the third parameter rather than specifying a
negative number:

SELECT substring(B'1111000000000001' from 5);
substring
--------------
000000000001
(1 row)

SELECT substring(B'1111000000000001' from 4);
substring
---------------
1000000000001
(1 row)

We have maintained nonstandard behavior in the past for
compatibility reasons, so it's a fair question; however, I'm
inclined toward the standard on this one.

-Kevin