split out file name from path in postgres
You can easily strip the path up to the last directory separator with an expression like
regexp_replace(path, '^.+[/\\]', '')This will match the ocassional forward slashes produced by some software as well. Then you just count the remaining file names like
WITH files AS ( SELECT regexp_replace(my_path, '^.+[/\\]', '') AS filename FROM my_table)SELECT filename, count(*) AS countFROM filesGROUP BY filenameHAVING count(*) >= 2;
CREATE OR REPLACE FUNCTION basename(text) RETURNS text AS $basename$declare FILE_PATH alias for $1; ret text;begin ret := regexp_replace(FILE_PATH,'^.+[/\\]', ''); return ret;end;$basename$ LANGUAGE plpgsql;