Pattern Matching question - PG 9.6

[Patrick B <patrickbakerbr@xxxxxxxxx>]

Hi guys,

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

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.