How to extract relative path from a url in postgresql?

@filiberto 

You can extract the relative path from a URL in PostgreSQL by using the regexp_replace function along with a regular expression pattern. Here's an example query that demonstrates how to extract the relative path from a URL column in a table:

1
2
3

SELECT url_column,
       regexp_replace(url_column, '^.+?//(.*?)/(.*)$', '/\2') as relative_path
FROM your_table_name;

In this query, url_column is the column that contains the URLs from which you want to extract the relative path. The regular expression pattern '^.+?//(.*?)/(.*)$' matches the domain and subdomain in the URL and captures the remaining path in the URL as the relative path.

The regexp_replace function is then used to replace the entire URL with just the captured relative path. The \2 in the replacement string refers to the second captured group in the regular expression pattern, which is the relative path.

You can customize the regular expression pattern according to the structure of your URLs if they differ from the example provided.