From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: pageorge@unice.fr, Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Re: Does the POSITION() function takes into account the COLLATION...
 or not ?!?
In-reply-to: 
 <CAKFQuwavkvERHKc=-Ws=1YEXy68j4GpJx0XbUwbKbY-=hKs7zQ@mail.gmail.com>
References: <164494187300.23318.373331246819207718@wrigleys.postgresql.org>
 <CAKFQuwavkvERHKc=-Ws=1YEXy68j4GpJx0XbUwbKbY-=hKs7zQ@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 16 Feb 2022 09:10:08 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3324020.1645028326.1@sss.pgh.pa.us>
Date: Wed, 16 Feb 2022 11:18:46 -0500
Message-ID: <3324021.1645028326@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3324021.1645028326%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Feb 15, 2022 at 11:17 AM PG Doc comments form <
> noreply@postgresql.org> wrote:
>> ==> up to here, this seems pretty enough to conclude that POSITION()
>> doesn't
>> care at all about COLLATION and always perform a byte search.

> How collations behave is documented, in particular:

> "A collation is either deterministic or nondeterministic. A deterministic
> collation uses deterministic comparisons, which means that it considers
> strings to be equal only if they consist of the same byte sequence."

Right.  POSITION does indeed do bytewise search, which is a legal
implementation for any deterministic collation, but perhaps not
for a nondeterministic one.  Thus, it throws a feature-not-supported
error if asked to use a nondeterministic collation.  As you say,
this is far from the only function that acts that way.

			regards, tom lane