A brief introduction to PostgreSQL in the array _ database other

PostgreSQL has many rich out-of-the-box data types, from standard numeric 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 would expect, can store array data in PostgreSQL, and with this feature you can implement storage requirements in a single table that require multiple tables in the past.

Why use arrays to store data, and if you're an application developer, use the same model in your database to store data in your program. Moreover, this approach can improve performance. Here we will describe how to use the PostgreSQL array type.

Suppose you purchase items on a website, the information you buy 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, you have an array field to hold multiple merchandise records, including:

• Number of items purchased
• Number
• Price

The SQL to insert data into this table is as follows:

INSERT into Purchases VALUES (1, Notoginseng, ' {{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 labels, which you can use to identify items you buy:

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 get the data:

SELECT title, Unnest (tags) items from

You can also use the Postgres Gin and Gist Index to quickly search for a product based on a specified label:

--Search where product contains tag IDs 1
and 2 SELECT
*  from product
where  tags @> array[1 2]
 
--Search where product contains tag IDs 1 OR 2
SELECT * from product
where  tags && Amp Array[1, 2]