CODEWITHSUNDEEP
sqlpostgresql
Crespo Wang
Crespo Wang

Reputation: 469

Postgres array field prepend string to each of the array element

In PostgresDB, I have an array field, it looks like this id | lesson | years 1 | math | {5,6} 2 | science | {4,5} How do I pre append a string, say year to each of the item in the years field,
select id, lesson, func_append_to_array_item(year) from table it returns id | lesson | years 1 | math | {year 5, year 6} 2 | science | {year 4, year 5}

Upvotes: 1

Views: 1819

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51599

if you want to just select it, you can use unnest + array_agg, eg:

t=# with c as (
  select id, lesson, concat('year ',unnest("year"))
  from "table"
)
select id, lesson,array_agg(concat) "year"
from c
group by id, lesson;
        year
---------------------
 {"year 5","year 6"}
(1 row)

But if you want to update the actual field, you first need to change array[] to array[]text in years column.

Also avoid reserved word in relation names please. Both year and table are SQL, not just words

update As soon as OP updated the post and reflecting comments:

build:

t=# create table s125(id int, lesson text, years int[]);
CREATE TABLE
t=# insert into s125 values (1,'math','{5,6}'),(2,'science','{4,3}');
INSERT 0 2
t=# create or replace function s126(_y int[]) returns table (years text[]) as $$
begin
return query with c as (
  select concat('year ',unnest(_y))
)
select array_agg(concat) "years"
from c
;
end;
$$ language plpgsql;
CREATE FUNCTION

run:

t=# select id,lesson,s126(years) from s125;
 id | lesson  |        s126
----+---------+---------------------
  1 | math    | {"year 5","year 6"}
  2 | science | {"year 4","year 3"}
(2 rows)

Upvotes: 2

Related Questions