arrays - SQLAlchemy（Postgres）ARRAY与JSON的整数列表(SQLAlchemy (Postgres) ARRAY vs JSON for list of integers)

Question

I want to store a list of integers in a Column with SQLAlchemy (using Postgres).

(我想用SQLAlchemy（使用Postgres）在列中存储整数列表。)

Am I better off using ARRAY or JSON?

(我最好使用ARRAY或JSON吗？)

My guess is ARRAY would have better performance in this case, but I don't see anything online confirming that.

(我的猜测是在这种情况下，ARRAY的性能会更好，但是我看不到任何在线确认这一点的信息。)

Additional question: What if I want the integers to be unique?

(附加问题：如果我希望整数唯一，该怎么办？)

  ask by thekthuser translate from so

Answer 1

PostgreSQL collects statistics on array membership for PostgreSQL arrays, but not for JSON arrays.

(PostgreSQL收集有关PostgreSQL阵列（而非JSON阵列）的数组成员资格的统计信息。)

This often leads to much better performance.

(这通常会导致更好的性能。)

Plus PostgreSQL arrays are typed, which seems like a benefit for your case.

(再加上PostgreSQL数组的类型，这似乎对您的情况有所帮助。)

Additional question: What if I want the integers to be unique?

(附加问题：如果我希望整数唯一，该怎么办？)

Can you expand on that?

(您能对此进行扩展吗？)

You want it to throw an error if you try to add a second copy of any one element?

(如果您尝试添加任何一个元素的第二个副本，是否希望它引发错误？)

Silently ignore the addition?

(默默无视加法？)

(Although neither case has in-place additions, it is really an assignment of the new array/JSONB which has happens to have one more element than the old one).

(（尽管这两种情况都没有就地添加，但这实际上是新数组/ JSONB的一种分配，恰好比旧数组多了一个元素）。)