Extract string in PostgreSQL 9.2

Question

I got the following column:

full_path "gorfs"."absolute_pathname"

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

enter image description here

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?

• user1822: meta.stackoverflow.com/questions/285551/…
• Erwin Brandstetter: Just this: stackoverflow.com/a/8612456/939860

Answer 1

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;

Answer 2

This turned out to be a tricky puzzle which took up far more time than I thought it would - I guess for you too? :-).

I (finally) managed to solve it thus: First I created (using DDL and DML for those who wish to try this at home) a table called stringtest:

CREATE TABLE stringtest (my_string VARCHAR(255));

and populated it as below

INSERT INTO stringtest VALUES('/blah/moreblah/4556/stuff/file.name');
INSERT INTO stringtest VALUES('/blah/moreblah/stuff/3434/file.name');
INSERT INTO stringtest VALUES('/users/test/34343/sfdsfs/6778');
INSERT INTO stringtest VALUES('/users/test/34343/sfdsfs/677990');

Then I used regexp_matches as I thought would be natural

ntest=# SELECT (regexp_matches(my_string, '\d+')) FROM stringtest;
 regexp_matches 
----------------
 {4556}
 {3434}
 {34343}
 {34343}
(4 rows)

So, I thought, this is easy-peasy - over in two seconds. How wrong was I!!

I then tried all sorts of typecasts (chained as well as single!), functions and assorted Googling to no avail.

The key to this problem is to realise that regexp_matches returns an array (hence the little curly braces around the fields) of strings. So, PostgreSQL (unsurpsingly perhaps) can't convert an array of strings into an integer. More frantic Googling ensued and I came across this gem.

The key is the array_to_string function. The answer I came up with is this:

ntest=# SELECT ((array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer) + 5 
AS IntTest FROM stringtest;
 inttest 
---------
 4561
 3439
 34348
 34348
(4 rows)

The +5 isn't strictly necessary (note the change in values) - it was just a test to prove that they are indeed treated as INTEGERs. Obviously, since they're account_ids, you won't be performing arithmetic on them.

[EDIT]

Following the OP's comment below about adding a WHERE clause, I tried the following. A simple WHERE (...array_to_string = value...) doesn't appear to work.

Next, from here, I tried

ntest=# CREATE FUNCTION colint(stringtest)
ntest-# RETURNS integer AS
ntest-# $func$
ntest$# SELECT (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer
ntest$# FROM stringtest
ntest$# $func$ LANGUAGE SQL STABLE;
CREATE FUNCTION

but, that doesn't work - appears to be a bug in PostgreSQL AFICS.

ntest=# select *, stringtest.colint from stringtest; 
 my_string | colint 
-------------------------------------+--------
 /blah/moreblah/4556/stuff/file.name | 4556
 /blah/moreblah/stuff/3434/file.name | 4556
 /users/test/34343/sfdsfs/6778 | 4556
 /users/test/34343/sfdsfs/677990 | 4556
(4 rows)

Definitely bad - same value for all of my function's derived values.

So, next I tried here using a CTE and bingo!

WITH tab AS
(
 SELECT *, (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer AS t
 FROM stringtest
)
SELECT * FROM tab
WHERE tab.t = 3434;

Result:

 my_string | t 
-------------------------------------+------
 /blah/moreblah/stuff/3434/file.name | 3434
(1 row)

Seems like a bit of a hack, but it gets the job done! :-) If you only want specific columns in stringtest, name the columns individually in tab - e.g.

ntest=# WITH tab AS
ntest-# (
ntest(# SELECT my_string, (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer AS t
ntest(# FROM stringtest
ntest(# )
ntest-# SELECT my_string FROM tab
ntest-# WHERE tab.t = 3434;
 my_string 
-------------------------------------
 /blah/moreblah/stuff/3434/file.name
(1 row)

Final thought, it might be just as well to have a TRIGGER on the table and do an INSERT into an additional field of just the account_id integer - that would make it very efficient for searching - it's a pity that PostgreSQL's [persisted | calculated | computed | generated] field functionality isn't up to the job - they are pretty cool when used correctly.

[FINAL EDIT]

I was thinking that this shouldn't be that tricky - and then I pondered Occam's razor - don't make things more complicated than they have to be - or K.I.S.S.. Would you believe that this works a treat?

CREATE VIEW vtest AS 
SELECT *, (SELECT ((array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer) 
AS account_id)
FROM stringtest;

Now the view:

ntest=# select * from vtest;
 my_string | account_id 
-------------------------------------+------------
 /blah/moreblah/4556/stuff/file.name | 4556
 /blah/moreblah/stuff/3434/file.name | 3434
 /users/test/34343/sfdsfs/6778 | 34343
 /users/test/34343/sfdsfs/677990 | 34343
(4 rows)

And, selecting from same with WHERE clause:

ntest=# select * from vtest where account_id = 4556;

gives (gulp... after all that! :-) )

 my_string | account_id 
-------------------------------------+------------
 /blah/moreblah/4556/stuff/file.name | 4556
(1 row)

• user83914: That's great!! Just one more thing: Can I add some WHERE clause to it? Like: WHERE id = 4561 ?
• Vérace: @JohnThomaz - check out my edit and final thought - HTH.
• user1822: You are right it does not need to be that complicated (or tricky). A simple split_part() will do.
• Erwin Brandstetter: but, that doesn't work - appears to be a bug in PostgreSQL AFICS. What does not work exactly? The function looks fine. (Except that all of this seems like overkill in the face of a simple split_part())

Answer 3

...or you can simply use parenthesis to isolate the components and cast as usual:

select ((regexp_matches(full_path, '\d+'))[1])::int
from gorfs.seg
where account_id = 4865

The question is: why do you need to extract the account_id from the full_path if you already have it in the where clause?