CODEWITHSUNDEEP
postgresqlpostgresql-9.6
eye_mew
eye_mew

Reputation: 9153

Postgres - Repeating an element N times as array

For example, where the element is 'hi', and where N is 3, I need a PostgreSQL snippet I can use in a SELECT query that returns the following array:

['hi', 'hi', 'hi']

Upvotes: 4

Views: 2061

Answers (3)

Tavin
Tavin

Reputation: 430

Postgres provides array_fill for this purpose, e.g.:

SELECT array_fill('hi'::text, '{3}');
SELECT array_fill('hi'::text, array[3]);

The two examples are equivalent but the 2nd form is more convenient if you wish to replace the dimension 3 with a variable.

See also: https://www.postgresql.org/docs/current/functions-array.html

Upvotes: 9

Kaushik Nayak
Kaushik Nayak

Reputation: 31746

You may use array_agg with generate_series

select array_agg(s) from ( values('hi')) as t(s) cross join generate_series(1,3)

Generic

select array_agg(s) from ( values(:elem)) as t(s) cross join generate_series(1,:n)

DEMO

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

sql demo

with cte as (
  select 'hi' as rep_word, generate_series(1, 3) as value
)                                         -- ^^^ n = 3
select array(SELECT rep_word::text from cte);

Upvotes: 0

Related Questions