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 not being 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
