Re: Pattern Matching question - PG 9.6

[Patrick B <patrickbakerbr@xxxxxxxxx>]

2017-05-15 15:20 GMT+12:00 Patrick B <patrickbakerbr@xxxxxxxxx>:

Hi guys,

Demo: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=3c3a3f870eb4d002c5b4200042b25669

FYI - NEW LINK http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ea61e7e1859bdb7f297f853a9dc0e3d0 with more variations.

 

AS you can see above, when performing this query:

SELECT * FROM test1 WHERE client_id = 10 AND path ~ '^/testfile/client/[0-9]+/attachment/(([0-9]{1,14})|(unassigned))/'

I get 5 rows. But actually I only want/need 3 of them:

• with the 'master' variation
• and if it is unassigned (attachment/unassigned); then i want it too

The rows that I should be getting are:

5   /testfile/client/10/attachment/1000/master/   10

7   /testfile/client/10/attachment/unassigned/file/1001/master   10

8   /testfile/client/10/attachment/unassigned/file/1002/master   10

What am I doing wrong?

Thanks

Patrick.