Thread: Position() Bug ? In PostgreSQL 9.2

This behavior is similar to strstr(3) ('needle in a haystack'
substring locating C function).

char *strstr(const char *haystack, const char *needle)

which returns haystack (that is main string) if needle (string to be
located) is empty.

Assuming position() tries to do something similar, it returns first
index (which is 1) of the main string (haystack!). Although this does
not answer if it's a bug or not, I tend to think it is deliberate.

On Thu, May 2, 2013 at 9:09 PM, dinesh kumar <dineshkumar02@gmail.com> wrote:
> Hello Team,
>
> I would like to know whether the following behavior is a BUG or an expected
> behavior. If this is a duplicated case, then kindly ignore.
>
> postgres=# SELECT version();
>                            version
> -------------------------------------------------------------
>  PostgreSQL 9.2.3, compiled by Visual C++ build 1600, 32-bit
> (1 row)
>
>
> postgres=# select position('P' in 'PostgreSQL');
>  position
> ----------
>         1
> (1 row)
>
>
> postgres=# select position('' in 'PostgreSQL'); // position(Substring as an
> empty string) is returning 1.
>  position
> ----------
>         1
> (1 row)
>
> Kindly let me know, if anything i miss here.
>
> Regards,
> Dinesh
> manojadinesh.blogspot.com



--

Amit Langote

dinesh kumar <dineshkumar02@gmail.com> writes:
> postgres=# select position('' in 'PostgreSQL'); *// position(Substring as
> an empty string) is returning 1.*
>  position
> ----------
>         1
> (1 row)

This is correct according to the SQL standard:

         <position expression> determines the first position, if any, at
         which one string, S1, occurs within another, S2. If S1 is of length
         zero, then it occurs at position 1 (one) for any value of S2. If S1
         does not occur in S2, then zero is returned.

            regards, tom lane

Hi Tom,

Thank you very much for the clarification.

Let me set an empty string validation,  before passing it to position() from API.

Thank you once again.

Regards,
Dinesh
manojadinesh.blogspot.com