Why array_agg() is returning empty array in postgresql?

Question

I have an integer type column named as start. I want to make an array by the values of this column. It seemed to be very easy and I used array_agg(), but it is giving empty array as output. Following is my column data

start
 1
 2
 11
 5
 .
 .
 . (and so on)

And following is my query used to make the array:

select array_agg(start) as start_array from table1;

Why is it giving empty array?

Is that really your entire query? You don't have any `WHERE` or other clause in addition to this? — Lukas Eder, Jan 09 '17 at 13:31
This should work. Are you sure the table really has data in it? — Gordon Linoff, Jan 09 '17 at 13:31
It should works: http://sqlfiddle.com/#!15/26e97/1/1 How did you check result? — Ivan Burlutskiy, Jan 09 '17 at 21:43
I don't know why the above query is giving me empty array. But the following query is running fine. `SELECT array(SELECT start FROM table1) as start_array`. What is the difference between array and array_agg? — LSG, Jan 10 '17 at 08:24

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

1

It's not

There is no way that this can return empty unless there are no rows. Perhaps a JOIN or a WHERE clause is wrong and you have 0-rows?

Also as a micro-optimization if your query is this simple,

select array_agg(start) as start_array from table1;

Then it's probably better written with the ARRAY() constructor...

SELECT ARRAY(SELECT start FROM table1) AS start_array;

edited Apr 13 '17 at 12:42

Community

1
1

answered Jan 10 '17 at 21:26

Evan Carroll

78,363
46
261
468

Why array_agg() is returning empty array in postgresql?

1 Answers1

It's not