CODEWITHSUNDEEP
postgresql
Wine Too
Wine Too

Reputation: 4655

PostgreSQL, result to array

In my program I have (almost) such a table:

CREATE TEMP TABLE temp_list 
   (profilenum integer, profilename text, compid text) 
   ON COMMIT DROP;
INSERT INTO temp_list 
VALUES (1, 'bynamePROFILE_border_Minimal',       '03'),
       (2, 'bynamePROFILE_backoffice_Universal', '03'),
       (3, 'bynamePROFILE_calc_Universal',       '03'),
       (4, 'bynamePROFILE_calc_Universal',       '02'),
       (5, 'bynamePROFILE_backoffice_Minimal',   '01'),
       (6, 'bynamePROFILE_ilup_Universal',       '03');

I can query it like this:

SELECT profilename  
  FROM temp_list WHERE compid='03' AND profilename LIKE 'bynamePROFILE_%';

... what gives expected result.
But I need some string manipulation to get wanted strings suitable to lately cast to array form in my program:

SELECT array_agg(btrim(profilename, 'bynamePROFILE_')) 
  FROM temp_list WHERE compid='03' AND profilename LIKE 'bynamePROFILE_%';

This gives me incorrect result:

"{order_Minimal,ckoffice_Universal,calc_Universal,ilup_Universal}"  
 (note missing first letter(s) in words beginning with 'b')

instead of:

"{border_Minimal,backoffice_Universal,calc_Universal,ilup_Universal}"

Is this because of me (as usual) or because of PostgreSQL and can my query be improved somehow to get wanted result?

Windows 10/64, PostgreSQL 9.6

Upvotes: 1

Views: 102

Answers (1)

klin
klin

Reputation: 121604

From the documentation:

btrim(string text [, characters text])

Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string.

If you want to remove the prefix abba, the function btrim() is not a correct choice:

with my_table (str) as (
values
    ('abbababax')
)

select btrim(str, 'abba')
from my_table;

 btrim 
-------
 x
(1 row)

Use one of other string manipulation functions, e.g. right():

with my_table (str) as (
values
    ('abbababax')
)

select right(str, -4)
-- or
-- select right(str, -length('abba'))
from my_table;

 right 
-------
 babax
(1 row)

Example of the use of regexp. Use ^ character to ensure you are removing the substring from the beginning of the string:

select regexp_replace(str, '^abba', '')
from my_table;

Upvotes: 3

Related Questions