How to perform sequential fetch in PostgreSQL?

Question

PostgreSQL

I need to make a request to the database and get an entry with path='/a/b/c/d' if there is no such entry, then path='/a/b/c' and so on. That is, at the output I have one record that was found in that order.
How to implement it?

Answer 1

prepare a list of possible paths

SELECT * FROM table 
WHERE path IN ('/a/b/c/d', '/a/b/c', ...) ORDER BY char_length(path) DESC LIMIT 1;

Well, do not forget to do an index by char_length (path) ..

CREATE INDEX ON TABLE (path);
CREATE INDEX ON TABLE (char_length(path));