How reliable is ordering in a Postgres array?

Question

I have store table:

CREATE TABLE store(
  id SERIAL,
  product_ids INT[],
  product_prices NUMERIC[]
)

For example:

id | product_ids | product_prices 
----------------------------------
1  | {12, 4}     | {100, 202.5}

Which means that the price for the product with ID 12 is 100, and the price for product ID 4 is 202.5. IDs in the product_ids array are unique.

If product id=4 has a new price, for example 199, I update it this way:

UPDATE store SET 
product_prices [idx(product_ids , 4)] = 199
WHERE id = ?

The order of elements in these arrays is very important. I never sort these arrays, maybe just add a new product_id and the appropriate price using array_append().

So the question is: How reliable is the ordering in a Postgres array? Is there a chance that somehow Postgres will sort the array or exchang element positions itself someday?

P.S. I need to make this with arrays, I don't want to use some other table with columns store_id, product_id, product_price etc.

What is the reason for this de-normalized design? You can't even have proper foreign keys with this model. Or prevent the same product to appear twice with different prices. And it will get quite complicated if you ever need to run (real-world) reports on it. — user330315
– user330315, Commented Dec 19, 2015 at 16:29
Reason is that quantity of self stores, is huge, approximately 500 000, but products for each store, would be not so big (max 5000), so if store this data with normal database design, e.i. in different table, the rows count will be very huge, in this case, product_ids and product_prices arrays will be contain maximum 5000 element, no more. — Oto Shavadze
– Oto Shavadze, Commented Dec 19, 2015 at 16:59
Or prevent the same product to appear twice with different prices - before adding product id to product_ids array, I check, if there exists this ID in array, i do price update as I show in question , if not, then I add new product id and its price at the end of appropriate arrays — Oto Shavadze
– Oto Shavadze, Commented Dec 19, 2015 at 17:00
And it will get quite complicated if you ever need to run (real-world) reports on it - because of reports will be from only one concrete store (and not from several stores together), executing data and making report will be easy, because of little amount of products for this concrete store — Oto Shavadze
– Oto Shavadze, Commented Dec 19, 2015 at 17:16
@a_horse_with_no_name This is reasons of this de-normalized design :) — Oto Shavadze
– Oto Shavadze, Commented Dec 19, 2015 at 17:17

Kristján · Accepted Answer · 2015-12-19 16:21:21Z

2

No, Postgres won't change the order of your arrays on its own. If a database did that, we would all be in a lot of trouble.

answered Dec 19, 2015 at 16:21

Kristján

19k5 gold badges55 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

How reliable is ordering in a Postgres array?

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