Why are there different types of arrays in postgreSQL?

Question

I have this query select array_agg(id) as idtag FROM POdelivery_a where ....

It gives me id in array: {26053,26021,26055} I use it later in other queries...

for the question assume I use it like this:

select * from a where id in {26053,26021,26055}

it gives me an error:

ERROR: syntax error at or near "{"

it will accept the query as:

select * from a where d in (26053,26021,26055)

So why array_agg(id) returns me an array that I can not work with? I always need to do conversions...

is there a way that array_agg(id) will return the result as (26053,26021,26055) not as {26053,26021,26055}?

Why does PostgreSQL works with many kinds of arrays?

(26053,26021,26055) is not an array. And {26053,26021,26055} is just the text representation of an array (explained in the manual: postgresql.org/docs/current/static/arrays.html#ARRAYS-IO) — user330315
– user330315, Commented Aug 10, 2015 at 10:07
Is there a function that can return data in form of (26053,26021,26055)? What i'm doing now is take the {26053,26021,26055} and replace the Brackets... its not very stylish... — John
– John, Commented Aug 10, 2015 at 10:14
Don't aggregate the result into an array: where id in (select id from ...) — user330315
– user330315, Commented Aug 10, 2015 at 10:18
The query I showed select array_agg(id) as idtag FROM POdelivery_a where .... isn't my real query... it's was used to explain my situation. The real query is over 20 lines. I care about the result: {26053,26021,26055} and the result will be used in many other places... i won't run the query every time.. I need to save the results.... — John
– John, Commented Aug 10, 2015 at 10:31
Then show us the complete query. To re-use the result of a sub-query you can use a common table expression — user330315
– user330315, Commented Aug 10, 2015 at 10:49

Pavel Stehule · Accepted Answer · 2015-08-10 10:41:06Z

1

There is a overlap of PostgreSQL arrays and SQL lists (sets). These types are very similar in modern SQL, but in typical usage there is semantic gap. A array is one no atomic value, that can be calculated and stored. A list (set) is usually multirow result of some subselect (can be stored in modern SQL too).

Examples:

-- the array constructors
SELECT ARRAY[1,2,3]
SELECT array_agg(someval) FROM tab

-- the list constructor
VALUES(1),(2),(3)
SELECT id FROM tab

-- filtering
SELECT * FROM tab WHERE id = ANY(ARRAY[1,2,3])
SELECT * FROM tab WHERE id IN (1,2,3)
SELECT * FROM tab WHERE id = ANY (SELECT array_agg(...))
SELECT * FROM tab WHERE id IN (SELECT idf FROM ...)

answered Aug 10, 2015 at 10:41

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Why are there different types of arrays in postgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related