Concatenate multiple arrays in Postgres

Question

Is there a way to concatenate multiple arrays into one array in Postgres?

For example, something like this:

ARRAY_CAT(
    ARRAY_FILL(5, ARRAY[4]),
    ARRAY_FILL(2, ARRAY[3]),
    ARRAY_FILL(11, ARRAY[3])
)

For this example, I'd like to see an output of

[5,5,5,5,2,2,2,11,11,11]

Clodoaldo Neto · Accepted Answer · 2017-08-18 20:08:41Z

25

select 
    array_fill(5, array[4]) ||
    array_fill(2, array[3]) ||
    array_fill(11, array[3])

answered Aug 18, 2017 at 20:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laposhasú Acsa · Accepted Answer · 2017-08-19 10:43:45Z

5

You can do it with repeated concatenations:

ARRAY_CAT(
  ARRAY_CAT(
    ARRAY_FILL(5, ARRAY[4]),
    ARRAY_FILL(2, ARRAY[3])
  ),
  ARRAY_FILL(11, ARRAY[3])
)

As @Clodoaldo Neto pointed out, you can use the || operator too. You can choose freely from these two, but keep in mind:

|| can behave differently depending on the operand types. This can cause confusion.
in the case when both operands are array, || is just an alias to array_cat. (source)

answered Aug 18, 2017 at 19:55

Laposhasú Acsa

1,59017 silver badges18 bronze badges