0

all.

I'm querying a Postgres 9.3 database looking for a specific pattern in a field:

SELECT
 P.id, P.processo_id, PR.num_formated
FROM
 publications P
INNER JOIN processos PR ON PR.id=P.processo_id
WHERE
 --The first numeric sequence must be exact 4 digits length, so the initial \d{4}
 PR.num_formated ~ '\d{4}[\.\-\\]\d{2}\.\d{4}\.\d{1}\.\d{2}\.\d{4}'
AND
 P.id=781291700 --Just to force a specific record

where PR.num_formated is defined as "character varying(255)". After being executed, the query returns:

  P.id      P.processo_id        PR.num_formated
781291700     502707245    20190001418-14.1998.8.05.0103

My question is: Why is Postgres "ignoring" the first \d? Is there any specificity in the form it interprets the regular expressions that differ from the "traditional/regular/orthodox/whatever" way, since the same regex works perfectly in another part of my system, but using a ruby code?

Thanks in advance

Walid

Walid
  • 31
  • 1
  • 4
  • What is the expected output? The result seems to match the regular expression you give. – Jeremy Nov 01 '19 at 18:54
  • You should write your example in a way that doesn't require us to have access to your computer in order to test it. `select '20190001418-14.1998.8.05.0103' ~ '\d{4}[\.\-\\]\d{2}\.\d{4}\.\d{1}\.\d{2}\.\d{4}' ;` – jjanes Nov 01 '19 at 19:47
  • This regular expression behaves the same way in ruby as it does in postgresql. I get a match for `ruby -le 'print "20190001418-14.1998.8.05.0103" =~ /\d{4}[\.\-\\]\d{2}\.\d{4}\.\d{1}\.\d{2}\.\d{4}/'` – jjanes Nov 01 '19 at 19:53

1 Answers1

1

The first 7 chars are ignored because the query finds the pattern as a part of the string. If you want to match the whole string use ^ and $ constraints.

'^\d{4}[\.\-\\]\d{2}\.\d{4}\.\d{1}\.\d{2}\.\d{4}$'
klin
  • 112,967
  • 15
  • 204
  • 232
  • You're right, man. I don't believe I miss such basic thing. What a rookie mistake! Thanks for the patience. – Walid Nov 02 '19 at 19:57
  • This mistake is quite common and explainable because people more often use the `LIKE` operator which treats the matter differently. – klin Nov 02 '19 at 21:14