Re: Reg: SQL Query for Postgres 8.4.3

Robert Haas <robertmhaas@gmail.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