does postgresql array type preserve order of array?

Question

I read over the docs for PostgreSQL v 9.3 arrays (http://www.postgresql.org/docs/9.3/static/arrays.html), but I don't see the question of ordering covered. Can someone confirm that Postgres preserves the insertion order/original order of an array when it's inserted into an array column? This seems to be the case but I would like absolute confirmation.

Thank you.

a) I don't see explicit documentation. b) It wouldn't make sense to change the order. The example table "sal_emp" has an array of quarterly pay; if "pay_by_quarter[1]" sometimes returned the value for the first quarter and sometimes for the fourth quarter, heads would have exploded by now. AFAIK, array data types in every language preserves order. c) For explicit confirmation, you'll probably have to check source code or the pghacker mailing list. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 3, 2015 at 22:58

Charles Duffy · Accepted Answer · 2015-08-13 17:10:33Z

18

The documentation is entirely clear that arrays are useful in scenarios where order is important, inasmuch as it explicitly documents querying against specific positions within an array. If those positions were not reliable, these queries would have no meaning. (Using the word "array" is also clear on this point, being as it is a term of the art: An array is an ordered datatype by its nature; an unordered collection allowed to contain duplicates would be a bag, not an array, just as an unordered collection in which duplicates were not allowed would be a set).

See the examples given in section 8.1.4.3, of "pay by quarter", with index position within the array indicating which quarter is being queried against.

answered Aug 13, 2015 at 17:10

Charles Duffy

299k43 gold badges441 silver badges497 bronze badges

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

Comments

user_0 · Accepted Answer · 2015-02-03 20:14:01Z

-2

Cannot find in documentation, but I'm pretty sure. Yes, order is preserved. And [2,4,5] is different from [5,2,4].
In case I'm wrong, indexes cannot work.

answered Feb 3, 2015 at 20:14

user_0

3,38323 silver badges38 bronze badges

1 Comment

Mike Sherrill 'Cat Recall' Over a year ago

"Cannot find in documentation, but I'm pretty sure." Well, that's basically the OP's question, isn't it?

Collectives™ on Stack Overflow

does postgresql array type preserve order of array?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related