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 Tsun05/11/2017 07:24:04Commented 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)
-
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} ```Crespo Wang– Crespo Wang05/11/2017 08:16:28Commented May 11, 2017 at 8:16
-
got it - modified to include your fieldsVao Tsun– Vao Tsun05/11/2017 08:30:45Commented May 11, 2017 at 8:30
-
i would like to note that ARRAY_AGG doesn't guarantee the order, so you should also provide an ORDER BY statementncank– ncank05/11/2017 21:13:02Commented May 11, 2017 at 21:13