Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

size/length limitation of array type in PostgreSQL

I'm working on a web project with PostgreSQL as a databases. I'm trying to build a structure of the web's databases that include a vector space model table. I created a table with attribute terms and docId[] where docId is the document ID of the term. Type of the docId is integer[]. So I can input a term with the document list that include the term in one single array. But the docId's array of term may contain a lot of entries.

So my question is: what is the max size of a 1-dimension array in Postgres?

like image 391
aditsud Avatar asked Apr 21 '15 07:04

aditsud


People also ask

What is array length in PostgreSQL?

PostgreSQL ARRAY_LENGTH() function This function is used to return the length of the requested array dimension. Syntax: array_length(anyarray, int) Return Type : PostgreSQL Version: 9.3. Example: PostgreSQL ARRAY_LENGTH() function.

What is array type in PostgreSQL?

The Array Type With the Array PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

Are PostgreSQL array zero based?

After some research, it turns out that PostgreSQL arrays are one-based: The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].


1 Answers

There is no size limit on Postgres arrays. There must be limits on row or column size, but that would run in the millions of entries.

A more SQL way to relate term to document is a 1 to many relation. This is implemented like:

table term: columns term_id, term, document_id
table document: columns document_id, summary, ...

The document_id column in the term table is called a foreign key.

like image 190
Andomar Avatar answered Sep 20 '22 04:09

Andomar