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}
-
1if the array is int[], you cantVao Tsun– Vao Tsun2017-05-11 07:24:04 +00:00Commented May 11, 2017 at 7:24
Add a comment
|
1 Answer
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)
3 Comments
Crespo Wang
Sorry I am not a SQL guru, how do I write the SQL, so that it returns something like this ``` 1 | math | {year 5, year 6} 2 | science | {year 2, year 3} ```
Vao Tsun
got it - modified to include your fields
ncank
i would like to note that ARRAY_AGG doesn't guarantee the order, so you should also provide an ORDER BY statement