How to extract file extension from filepath field in PostgreSQL?

need to extract file extensions from the file_path column from my PostgreSQL table. kindly help me to achieve to extract the distinct file extensions as list using select query.

have table like this

expected output like is

my query

select reverse(substring(reverse(file_path) from 1 for strpos(reverse(file_path),'.')-1)) as extension from pgsql_table_files;

A simple solution using split_part from here String Functions.

create table file_ext(id integer, file_path varchar);
insert into file_ext values (1, 'https://example.com/uploads/profile_pic.jpeg'), 
(2, 'https://example.com/public/upload/resume.png'), 
(3, 'https://example.com/public/upload/cover_letter.png'), 
(4, 'https://example.com/uploads/cat_pic.jpeg');

select * from file_ext ;
 id |                     file_path                      
----+----------------------------------------------------
  1 | https://example.com/uploads/profile_pic.jpeg
  2 | https://example.com/public/upload/resume.png
  3 | https://example.com/public/upload/cover_letter.png
  4 | https://example.com/uploads/cat_pic.jpeg


select distinct split_part(file_path , '.', -1) as extension from file_ext;
 extension
------------
 png
 jpeg

Use split_part to break the string on . and then use reverse indexing(-1) to fetch the last split which is the extension. Then use DISTINCT to fetch only one record for each extension.

If you want to include a row number then:

select 
   distinct on(split_part(file_path , '.', -1)) row_number() over() as row_no, 
   split_part(file_path , '.', -1) as extension from file_ext;

 row_no | extension 
--------+------------
      1 | jpeg
      2 | png