REGEXP in a where clause
~ POSIX Regex Operator

The easy way to do this is to use the ~ operator.

SELECT *
FROM ( VALUES
  ( '/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/photo.jpg' ),
  ( '/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/small/photo.jpg' ),
  ( '/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main' ),
  ( '/{s3bucket}/filesuser/client/27801125/attachment/4512/file' ),
  ( '/{s3bucket}/filesuser/client/27801126/attachment/4513/main' )
) AS t(path_name)
WHERE path_name ~ '/main$';


You can always be more specific with your regex..

WHERE path_name ~ '/filesuser/client/\d+/attachment/\d+/main$';