Re: regex failing

On Tue, 27 Jun 2023 at 14:59, Zahir Lalani <ZahirLalani@oliver.agency> wrote:
>
> Hi All
>
>
>
> Got a weird one. I am using the regex below to case match numeric only values.
>
>
>
> '^([0-9]+[.]?[0-9]*)$'
>
>
>
> This works well by and large but not for a value like “1234:567”. This seems to match positive and then fails due to
notbeing an actual number in the subsequent cast. 
>
>
>
> Any ideas? (PG13)
>
>
>
> Z
>
>
When executed from psql it works ok. What tool did you use to run your
query? I would suspect that the tool does interpret square brackets
somehow and your regex actually becomes  '^([0-9]+.?[0-9]*)$' which
matches any character between digits.

If you enable query logging in your system - either uncomment
log_statement = 'all' in postgresql.conf  or run ALTER SYSTEM SET
log_statement = 'all'; and restart, then you will find the actual
query in server's log file:
2023-06-27 16:12:35.221 EDT [23580] LOG:  statement: select '1234:56'
~ '^([0-9]+[.]?[0-9]*)$';

make sure it remains the same.
--
Sergey