← home

postgres array stuff

A procedure that takes an array argument and inserts a row for each array element

Use generate_series.

insert into foo (
    select myarray[i]
    from generate_series(1, array_upper(myarray, 1)) as i
)

Also seen here, this can be made into an explode_array function.

create function explode_array(myarray anyarray) returns setof anyelement as
$$
    select ($1)[i] from generate_series(1, array_upper($1, 1)) as i;
$$ language sql immutable;

Using this, the original query would become:

insert into foo (
    select * from explode_array(myarray)
)

Matching existing records to an array of possible matches

Just use "IN".

update foo set a = true where b in (myarray)

Array-building aggregate function

Also in the link mentioned above is a reference to the array_accum function that exists in the Postgres documentation.

select
    post.name,
    array_to_string(array_accum(tag.name), ' ') as tags
from
    post
    inner join post_tags as pt on pt.post_id = post.id
    inner join tag on tag.id = pt.tag_id
where
    post.id = ?
group by
    post.name

Nick Welch <nick@incise.org> · github