Extract string in PostgreSQL 9.2 I got the following column:

full_path "gorfs"."absolute_pathname"


It keeps the path of a BLOB, like on the example below:



Note that the 4865 is the account_id.The path is always like: /userfiles/account/ACCOUNTID

I need to extract that, to use in another query. For example:

select * from gorfs.seg WHERE account_id = 4865


How can I extract that value from the full_path column?
If the account id is always at the third position use split_part()

select * 
from gorfs.seg 
WHERE split_part(full_path, '/', 4) = '4865'


Note that due to the leading / the actual index is 4, not 3.

The following query: 

with seg (id, full_path) as (
  values 
      (1, '/userfiles/account/4556/attachment/1234'), 
      (2, '/userfiles/account/123/attachment/1234'), 
      (3, '/userfiles/account/7890/attachment/1234'),
      (4, '/userfiles/account/4556/attachment/56789')
)
select *
from seg
where split_part(full_path, '/', 4) = '4556'


returns

id | full_path                               
---+-----------------------------------------
 1 | /userfiles/account/4556/attachment/1234 
 4 | /userfiles/account/4556/attachment/56789


If you want to put that into a view, you can use:

create or replace view v_seg
as
select st_ino, 
       segment_index, 
       st_ino_target, 
       full_path, 
       split_part(full_path, '/', 4) as account_id
from gorfs.seg;