Extract String in PostgreSQL 9.2 – How to Guide

postgresqlstring

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?

Best Answer

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;

Related Solutions

PostgreSQL failed on Select

I think it should be;

    DECLARE 
      _port int;
    BEGIN
      select alter_port into _port from a_table where ....;
      select data into data_list from a_table where PortNumber = _port;

Insert Multiple Rows and Columns in PostgreSQL 9.2

If it's guaranteed (like you commented on your previous question) that each SELECT returns exactly 1 row, the simplest way to combine both would be to just nest one SELECT within the other:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id
    , (SELECT id
       FROM   public.ja_mobiusers
       WHERE  name_first LIKE 'Alisha%'
       AND    name_last LIKE 'Dson%') AS foo  -- alias irrelevant
FROM   junk.wm_260_billables2
WHERE  info ILIKE '%Alisha%';

If there can be 0 - n results per SELECT, it gets more complex. You would have to define what the result should look like for each possible combination.

Note that you get no row (nothing inserted) if the outer SELECT finds nothing, but a NULL value for the second column if the nested SELECT finds nothing.

Or you could just use two subqueries:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
VALUES (
  (SELECT billable_id
   FROM   junk.wm_260_billables2
   WHERE  info ILIKE '%Alisha%')
, (SELECT id
   FROM   public.ja_mobiusers
   WHERE  name_first LIKE 'Alisha%'
   AND    name_last LIKE 'Dson%')
   );

Now you get NULL for each column where nothing is found, and an error if more than one row is found.

I seriously doubt that an ILIKE expression can guarantee a single result row.

Aside: your constraint would be faster and cleaner this way - though more verbose:

ALTER TABLE dm.billables_links ADD CONSTRAINT cc_one_and_only_one_target
CHECK (customer_id IS NOT NULL AND role_id IS     NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS NOT NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS     NULL AND mobiuser_id IS NOT NULL)

Best Answer

Related Solutions

PostgreSQL failed on Select

Insert Multiple Rows and Columns in PostgreSQL 9.2

Related Question