PostgreSQL is one of our favorite databases. More than just a relational database, it also adds support for JSON data, full-text retrieval capabilities, and other extensions.

Fourth overall in February 2020 and fourth in relational databases. For ranking information: DB-Engines

In order to better application of the work, for PostgreSQL caused by the confusion of the following content:

(1) The main differences between PostgreSQL versions

(2) Data types supported by PostgreSQL

(3) Introduction to PostgreSQL storage engine Heap

(4) Full text retrieval around JSON/JSONB and JSON/JSONB Path in 12.x

1, PostgreSQLMajor differences between versions

When we go to the PostgreSQL website in February 2020 and see multiple releases, what should we do?

The February 2020 release of PostgreSQL 9.x is one of the planned releases, so I reviewed the PostgreSQL documentation and sorted out the PostgreSQL 10.x, 11.x, and 12.x releases. The diagram below:

From the above release, we found that the changes for developers were minor. Here are four technical points that developers often focus on:

(1) Currently supported data types

(2) Storage engine Heap specific features

(3) Full text retrieval around JSON/JSONB and JSON/JSONB Path in 12.x

Therefore, the following paragraphs will focus on the above four technical points.

Here is a PostgreSQL Docker image with version 12.2:

Docker pull postgre: 12.2Copy the code

If you are interested in other versions you can visit the Docker Hub

Start the Postgre

Docker run -e POSTGRES_PASSWORD=123456 -p 5432:5432 --name potgre12 postgres:12.2Copy the code

-e POSTGRES_PASSWORD=123456 is the password of user superuser. The default username of user superuser is Postgres.

POSTGRES_HOST_AUTH_METHOD=trust to allow password-free access.

Install the PSQL client (skip this step if you are using another client)

brew install libpq
Copy the code

Connect the postgreSQL

PSQL -h 127.0.0.1 -p 5432 -u postgres --passswordCopy the code

2. PostgreSQL supports data types

PostgreSQL supports multiple data types. The following figure

PostgreSQL storage engine Heap features

PostgreSQL’s storage engine Heap and MySQL’s InnoDB both follow ACID exactly and use row-level locking.

4. Full text retrieval around JSON/JSONB and JSON/JSONB Path in 12.x

The difference between JSON and JSONB is that:

(1) JSON type To store text data in JSON format, in the query time need to do content analysis first, and then query. So writing is fast and querying is slow.

(2) JSONB type stores data in binary form of JSON format. It needs to do text parsing when inserting, but does not need to do parsing when querying, so writing is slow and querying is fast.

For JSON/JSONB, the following is a simple example of a piece of data: create a table, create data, query, fuzzy query, update data.

(1) Build a table

create table boy (
    id int primary key not null, 
    name char(30) not null, 
    information jsonb
);
Copy the code

(2) Create new data

insert into boy (id.name, information) values (1.'Page'.'{"age":18}');
insert into boy (id.name, information) values (2.'Aaron'.'{"age":18}');
insert into boy (id.name, information) values (3.'Join'.'{"age":18, "address":"beijing dongchengqu"}');
Copy the code

(3) Query

Student select * from boy where (information->>'age')::int=18; Select * from boy where (information->>'address')::text like students select * from boy where (information->>'address')::text like 'beijing%'; Student select jsonb_path_query(information, '$.age? (@==18)') from boy; Students select jsonb_path_query(information, '$.address? (@ starts with "beijing")') from boy;Copy the code

As you can see from the above code, the JSON function provided in PostgreSQL 12 is very useful. See json functions for more query statements

(4) Fuzzy query (full-text retrieval)

The default PostgreSQL word segmentation is not good for Chinese word segmentation. So the following will demonstrate full text retrieval in English

The new table

create table technical (
   id int primary key not null.name tsvector
);
Copy the code

The data type of tsVector is the toggle type. Select ‘hello page’::tsvector; To see the word segmentation results.

Insert data


insert into technical (id.name) values (1.'Test Driven Development'), (2.'Test Driven Design'), (3.'Behave Driven Development');

Copy the code

Search keywords Test and Driven should show two results;

select * from technical where name@ @'Test & Driven';
Copy the code

Conclusion:

PostgreSQL has a lot of functionality and functions that you need to understand before you get started.

reference

(1) PostgreSQL Chinese