Create an array by repeating a single value a given number of times.
ARRAY_REPEAT(element, count)## Overview Returns an ARRAY containing the supplied element repeated exactly count times. ARRAY_REPEAT is the one-dimensional specialisation of ARRAY_FILL and is the canonical constructor for constant vectors of a known length, whether as zero-padded accumulators, test fixtures, or placeholder arrays attached to a join result. Typical uses include building per-row zero vectors sized by another column's length, padding a shorter array to a target size, and generating constant arrays inline for parameterised tests. ## Behavior - Returns an ARRAY<T> of length count, with every element equal to the supplied value. - Count = 0 produces an empty array of type ARRAY<T>. - Count is required to be non-negative. A negative count raises an error. - Element type is inferred from the argument; use an explicit CAST when repeating a NULL to pin the type. - Never mutates arguments. ## Null and empty handling - NULL element (with an explicit type cast) produces an array of count NULLs. - NULL count raises an error; the size must be explicit. - Count = 0 returns an empty array; no error is raised. - Extremely large counts succeed syntactically but may exhaust memory because the array is materialised eagerly. ## Compatibility - Matches the array/map SQL convention for ARRAY_REPEAT. Equivalent to ARRAY_FILL(element, ARRAY(count)) for the one-dimensional case. - The SEQUENCE function can produce arithmetic sequences when the value must vary; ARRAY_REPEAT covers only the constant case.
| Name | Type | Description |
|---|---|---|
element | Specifies the value to repeat. Accepts any scalar, struct, or nested type; NULL is allowed. | |
count | Specifies the number of copies. Must be a non-negative integer. Zero produces an empty array; a negative value raises an error. |
-- Repeat an integer
SELECT ARRAY_REPEAT(7, 3); -- [7, 7, 7]-- Repeat a string for padding
SELECT ARRAY_REPEAT('x', 5); -- ['x', 'x', 'x', 'x', 'x']-- Zero repetitions yields an empty array
SELECT ARRAY_REPEAT(1, 0); -- []-- Repeat NULL with an explicit type
SELECT ARRAY_REPEAT(CAST(NULL AS INT), 3); -- [NULL, NULL, NULL]-- Per-session zero vector sized by the trail length
SELECT session_id, ARRAY_REPEAT(0, ARRAY_SIZE(events)) AS zero_vector
FROM analytics.events.user_sessions;-- Synthesize constant arrays for testing
SELECT ARRAY_REPEAT(CAST('pending' AS STRING), 10) AS statuses;