PostgreSQL, result to array

Question

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 28, 2018 at 12:59

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

jcaron Over a year ago

The best choice would probably be regexp_replace.

klin Over a year ago

@jcaron - probably not, as regexp functions are more expensive.

jcaron Over a year ago

I mean in terms of resistance to various inputs and later code changes. But of course it depends on how many rows are to be processed.

klin Over a year ago

Good point about resistance to various inputs though it can be easily resolved, see the updated answer.

Wine Too Over a year ago

I see, thanks 'klin'. Would you belive me or not this code with btrim works in my program for a few past years :) Right string works OK. I would like to see example with regex replace too if possible.

|

Collectives™ on Stack Overflow

PostgreSQL, result to array

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related