Postgres array concatenation example

Array concatenation in Postgres is really simple, although the operator is non-obvious: ||

This works for several syntactically similar combination of scalars and arrays, as seen here:

select ARRAY['b', 'c', 'd'] || ARRAY['e'];
select ARRAY['b', 'c', 'd'] || '{e}';
select '{a}' || ARRAY['b', 'c', 'd'];
select ARRAY['a'] || ARRAY['b', 'c', 'd'];
select array_cat('{a}'::text[], '{b,c,d}'::text[])
select 1 || ARRAY[2,3,4]

If you try scalar + array combinations with strings, you’ll get an error like so:

ERROR: array value must start with "{" or dimension information Position: 8

This is because Postgres doesn’t know whether or not to coerce a string you’ve provided into it’s array form. The only workaround I know for this is to make a single-element array from the value.

Leave a Reply

Your email address will not be published. Required fields are marked *