An example is provided to briefly introduce arrays in PostgreSQL and postgresql.

An example is provided to briefly introduce arrays in PostgreSQL and postgresql.

PostgreSQL has many out-of-the-box data types, from standard digital data types, to geometric types, and even network data types. Although many people ignore these data types, they are one of my favorite features. The array data type, as you expected, can be used to store array data in PostgreSQL. With this feature, you can implement the previously required storage requirements for multiple tables in a single table.

Why use arrays to store data? If you are an application developer, you can use the same model in the database to store data in the program. In addition, such an approach can improve performance. The following describes how to use the PostgreSQL array type.

If you purchase an item on a website, the information you have purchased can be expressed in the following table:
 

CREATE TABLE purchases (  id integer NOT NULL,  user_id integer,  items decimal(10,2) [100][1],  occurred_at timestamp);

In this table, there is an array field to maintain multiple product records, including:

• ID of the purchased item
• Quantity
• Price

The SQL statement for inserting data into this table is as follows:
 
Insert into purchases VALUES (1, 37, '{15.0, 1.0, 25.0}, {15.0, 1.0, 25.0}', now ());
Insert into purchases VALUES (2, 2, '{11.0, 1.0, 4.99}', now ());
A more practical example is the use of tags. You can use tags to identify purchased items:

 

CREATE TABLE products (  id integer NOT NULL,  title character varying(255),  description text,  tags text[],  price numeric(10,2));

You can use basic query statements to obtain data:

 

SELECT title, unnest(tags) items FROM products

You can also use the Gin and Gist indexes of ipvs to quickly search for products based on specified tags:
 

-- Search where product contains tag ids 1 AND 2SELECT *FROM  productsWHERE  tags @> ARRAY[1, 2] -- Search where product contains tag ids 1 OR 2SELECT *FROM  productsWHERE  tags && ARRAY[1, 2]