Обсуждение: [GENERAL] Pattern Matching question - PG 9.6
AS you can see above, when performing this query:
I get 5 rows. But actually I only want/need 3 of them:SELECT * FROM test1 WHERE client_id = 10 AND path ~ '^/testfile/client/[0-9]+/attachment/(([0-9]{1,14})|(unassigned))/'
- 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.
2017-05-15 15:20 GMT+12:00 Patrick B <patrickbakerbr@gmail.com>:
FYI - NEW LINK http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ea61e7e1859bdb7f297f853a9dc0e3d0 with more variations.
I get 5 rows. But actually I only want/need 3 of them: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))/'
- 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.
On Sunday, May 14, 2017, Patrick B <patrickbakerbr@gmail.com> wrote:
Demo: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle= 3c3a3f870eb4d002c5b4200042b256 69
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?
Without you explaining why 6 and 9 are invalid it's impossible to say how you should modify your regex to exclude them. You may find positive and negative look-ahead useful though.
David J.
2017-05-15 16:10 GMT+12:00 David G. Johnston <david.g.johnston@gmail.com>:
On Sunday, May 14, 2017, Patrick B <patrickbakerbr@gmail.com> wrote:Demo: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=3c3a3f870eb4d0 02c5b4200042b25669
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?
Without you explaining why 6 and 9 are invalid it's impossible to say how you should modify your regex to exclude them. You may find positive and negative look-ahead useful though.David J.
I thought I have already explained it. Here it goes again. Demo page is: http://dbfiddle.uk/?rdbms=
As you can see, there is a lot of variations for the same file_id (1000). File_id (1001/1002) is a new unassigned file, different from the others.
I wanna be able to get ONLY the 'master' variation ( /testfile/client/10/attachment/1000/master/ ) and the unassigned files variations [if any] (/testfile/client/10/attachment/unassigned/file/1001/master | /testfile/client/10/attachment/unassigned/file/1002/master).
So on the demo above, only id IN (5,9,10) are valid for me. The SELECT that I used as an example is not returning me ONLY the data I need, instead, it is returning (almost) everything.
To summarize: I wanna use a pattern matching the only returns these rows:
/testfile/client/10/attachment/1000/master/
/testfile/client/10/attachment/unassigned/file/1001/master
/testfile/client/10/attachment/unassigned/file/1002/master
What can I do to fix it?
Thanks
P.
On Sunday, May 14, 2017, Patrick B <patrickbakerbr@gmail.com> wrote:
You'll need to be more verbose and literal if you truly want to exclude 7. Just add more path separators and patterns like [^/]+ until you get what you want.
I thought I have already explained it. Here it goes again. Demo page is: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ea61e7e1859bdb 7f297f853a9dc0e3d0
Now that you've posted the right link it becomes a bit easier to follow...
To summarize: I wanna use a pattern matching the only returns these rows:/testfile/client/10/attachment/1000/master/
/testfile/client/10/attachment/unassigned/file/ 1001/master
/testfile/client/10/attachment/unassigned/file/ 1002/master
Why isn't 7 valid?
Requiring that the last directory be the word master, with or without a forward slash, is simple enough but 7 matches that. Just tack the following onto the end of your existing pattern.
.+master/?$
I'd be more helpful but my iPad and the fiddle don't seem to play nicely together...
David J.
^/testfile/client/[0-9]+/attachment/([0-9]{1,14}/master/$|unassigned/)
Kind Regards
~Maciek
On 15 May 2017 at 06:21, Patrick B <patrickbakerbr@gmail.com> wrote:
2017-05-15 16:10 GMT+12:00 David G. Johnston <david.g.johnston@gmail.com>:On Sunday, May 14, 2017, Patrick B <patrickbakerbr@gmail.com> wrote:Demo: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=3c3a3f870eb4d0 02c5b4200042b25669
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?
Without you explaining why 6 and 9 are invalid it's impossible to say how you should modify your regex to exclude them. You may find positive and negative look-ahead useful though.David J.I thought I have already explained it. Here it goes again. Demo page is: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ea61e7e1859bdb 7f297f853a9dc0e3d0 As you can see, there is a lot of variations for the same file_id (1000). File_id (1001/1002) is a new unassigned file, different from the others.I wanna be able to get ONLY the 'master' variation ( /testfile/client/10/attachment/1000/master/ ) and the unassigned files variations [if any] (/testfile/client/10/ attachment/unassigned/file/ 1001/master | /testfile/client/10/ attachment/unassigned/file/ 1002/master). So on the demo above, only id IN (5,9,10) are valid for me. The SELECT that I used as an example is not returning me ONLY the data I need, instead, it is returning (almost) everything.To summarize: I wanna use a pattern matching the only returns these rows:/testfile/client/10/attachment/1000/master/
/testfile/client/10/attachment/unassigned/file/ 1001/master
/testfile/client/10/attachment/unassigned/file/ 1002/master What can I do to fix it?ThanksP.