From: Gilles Darold <gillesdarold@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: different result of regexp_instr than on Oracle
Date: Fri, 18 Sep 2026 09:57:05 +0200
Message-ID: <7df7835d-a587-4d8a-a415-72e66cde5aee@gmail.com> (raw)
In-Reply-To: <CAFj8pRAQu6cwOEqFFH7e2NCw_KupryvEn7eAJ3hGQ+EjfFzivA@mail.gmail.com>
References: <CAFj8pRAQu6cwOEqFFH7e2NCw_KupryvEn7eAJ3hGQ+EjfFzivA@mail.gmail.com>
Le 18/09/2026 à 07:21, Pavel Stehule a écrit :
> Hi
>
> I got an error report for regexp_instr in Orafce.
>
> SELECT REGEXP_INSTR('ABC', 'A', 1, 1, 0, '', 1) AS SUBEXPR_1
>
> Oracle returns 0, Postgres 1.
>
> Because Orafce now share this functionality with Postgres, there
> should be same problem
>
> Is this behaviour expected?
Hi Pavel,
That's a good catch. I have tested orafce with your commit 546ee72 it
now works as expected for Oracle compatibility. About PostgreSQL
regexp_instr() behavior, I think it is expected that it returns 1, per
the documentation:
"the subexpr is an integer indicating which subexpression is of
interest: the result identifies the position of the substring matching
that subexpression. Subexpressions are numbered in the order of their
leading parentheses. When subexpr is omitted or zero, the result
identifies the position of the whole match regardless of parenthesized
subexpressions".
When no capture group are defined in the pattern, there's an implicit
capture group for the whole match when subexpr is 0 or 1.
contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B', 1, 1, 0, '', 0) AS
SUBEXPR_0;
subexpr_0
-----------
2
contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B', 1, 1, 0, '', 1) AS
SUBEXPR_1;
subexpr_1
-----------
2
contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B', 1, 1, 0, '', 2) AS
SUBEXPR_2;
subexpr_2
-----------
0
contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B(C)', 1, 1, 0, '', 0)
AS SUBEXPR_0;
subexpr_0
-----------
2
(1 ligne)
contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B(C)', 1, 1, 0, '', 1)
AS SUBEXPR_1;
subexpr_1
-----------
3
Best regards,
--
Gilles
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: gillesdarold@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: different result of regexp_instr than on Oracle
In-Reply-To: <7df7835d-a587-4d8a-a415-72e66cde5aee@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox