Postgres regex to get single match

Question

I have a list of paths in Postgres:

/approve/optimal/roadmap/

/approve/optimal/roadmap/payments/

/approve/optimal/roadmap/payments/benefits/

/approve/optimal/roadmap/payments/healthcare/

I'd like a regex that will get the direct descendent of /approve/optimal/roadmap/ (ie: /approve/optimal/roadmap/payments/, but nothing else)

I have looked at the ~ operator, but can't seem to make it work with POSIX regex.

SELECT * FROM table WHERE path ~ '/approve/optimal/roadmap/.+?/^.+/'

If this is possible without regex, I'd love to implement THAT solution, as regex just gives me a headache.

Can you clarify, do you want to GET the value e.g. "payments", or do you want to get records WHERE path ~ ... ? — 404
– 404, Commented Apr 6, 2018 at 15:11
I would like the query to return records with the full path of direct descendants. — Nathan R
– Nathan R, Commented Apr 6, 2018 at 15:19

Aaron · Accepted Answer · 2018-04-06 15:14:34Z

2

You can use the following :

SELECT * FROM table WHERE path ~ '^/approve/optimal/roadmap/[^/]+/$'

answered Apr 6, 2018 at 15:14

Aaron

24.9k2 gold badges41 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, this is exactly what I needed. Saved me from ingesting more Advil.