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

Source: Internet
Author: User

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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.