The original article is reprinted from liu Yue’s Technology blog v3u.cn/a_id_171

Recently, the concept of “full stack database” has become very popular, and the leading role is PostgrelSQL. In recent years, PostgrelSQL has developed rapidly, covering OLTP, OLAP, NoSQL, search, image and other application scenarios. Help the company to solve the data island, multiple data platforms, synchronization and consistency, delay, software and hardware cost increase and other business pain points, in the Internet, finance, Internet of things, traditional enterprises and other fields have been widely used. PostgreSQL is often referred to as “the Oracle of open source” in the industry, as its application scenarios are as diverse as those of Oracle.

As for Mysql, everyone is familiar with it. Because of the talent pool and large amount of data, many companies generally follow the mode of Hadoop+Mysql. Hadoop calculates a large amount of original data, and then stores the display data summarized by dimension in Mysql. For example, the famous Emoji pit, the utF8MB4 pit (implicit type conversion trap) derived from this pit, the extremely low performance pessimistic locking mechanism, the lack of support for multiple form sequences with IDS, the lack of support for the over clause, and the almost no performance subquery…….. For more crimes, see: Damn choice: Mysql. And these problems, in PostgrelSQL has been improved, this time we in Win10 platform using Docker installation PostgrelSQL, and initially feel its charm.

The first step, of course, is to install Docker. If you are not familiar with it, please refer to: Playing with DockerToolBox under win10 system and replacing domestic image sources (various god pits).

Then pull the mirror, in this case choosing the relatively stable PostgrelSQL11.1.

Docker pull postgres: 11.1Copy the code

After the mirror is pulled, run the command to view the mirror

docker images
Copy the code

As you can see, its mirror image is very small, about 300m or so, much smaller than Mysql.

Then we can start the container and type in the command

Docker run -d --name dev-postgres -e POSTGRES_PASSWORD=root -p 6432:5432 postgres:11.1Copy the code

The default port number is 5432. Since a PostgrelSQL server has been installed on my host, this port number is changed to 6432 through port mapping.

Type the command

docker ps
Copy the code

To see the container running status

No problem. Now let’s go to the command line.

docker exec -it dev-postgres bash

psql -h localhost -U postgres


Copy the code

To view the database list, enter the command \l of PostgrelSQL on the command line inside the container.

Set up a database

CREATE DATABASE mytest;
Copy the code

Using a database

\c mytest
Copy the code

Create a table

CREATE TABLE "public"."article" (  
	"id" int4 NOT NULL,  
	"content" text,  
	PRIMARY KEY ("id")  
)  
WITH (OIDS=FALSE);
Copy the code

List all tables

\d
Copy the code

If you are not comfortable with the command line, you can also use visual tools such as Navicat for linking

Note that the default user is Postgres, but it is worth saying that navicat does not allow you to manually set auto-increment as Mysql does. PostgrelSQL uses the form of a sequence to increment its attributes:

CREATE SEQUENCE serial START 1;
Copy the code

The sequence created here is counted from 1.

Then, set the default value of the field you want to set to sequence growth

ALTER TABLE "public"."article" ALTER COLUMN "id" SET DEFAULT nextval('serial');
Copy the code

Emoji can be easily stored using UTF-8 encoding

To apply the over clause, suppose we have a table of employee salaries (department, employee ID, salary) :

postgres=# d empsal   
          Table "public.empsal"  
 Column  |       Type        | Modifiers   
---------+-------------------+-----------  
 depname | character varying |   
 empno   | integer           |   
 salary  | integer           |
Copy the code

Some data:

postgres=# select * from empsal ;  
  depname  | empno | salary   
-----------+-------+--------  
 develop   |    11 |   5200  
 develop   |     7 |   4200  
 develop   |     9 |   4500  
 develop   |     8 |   6000  
 develop   |    10 |   5200  
 personnel |     5 |   3500  
 personnel |     2 |   3900  
 sales     |     3 |   4800  
 sales     |     1 |   5000  
 sales     |     4 |   4800  
(10 rows)
Copy the code

Now I want to compare each employee’s salary with the average salary in his department. How do I do that? In fact, this is leetcode’s original problem, mysql can only use subqueries, while PostgrelSQL can easily implement this query

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsal;
Copy the code

Query result:

depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | | 4200 | 5020.0000000000000000 develop | | 4500 | 9 5020.0000000000000000 develop | 8 5020.0000000000000000 develop | | 6000 | | 5200 | 5020.0000000000000000 personnel | | 3500 | 3700.0000000000000000 5 3700.0000000000000000 sales personnel | | 3900 | 2 | 3 | 4800 | 4866.6666666666666667 sales | | 5000 | 1 4866.6666666666666667 the sales | | 4800 | 4866.6666666666666667 (10 rows)Copy the code

As you can see, in this query, the aggregation function AVG has the same meaning as the average value. But different from ordinary aggregate function, it does not average all salary in the table. Instead, it averages all salary in the same department (PARTITION BY depname), and the result is shared BY all rows in the same department. It does not merge the rows. This greatly simplifies the complexity of SQL, but also can be very convenient to solve the “each group take top K” such problems.

The problem with using a container to start a database is that every time the container stops, the data will be lost, so we can use the docker mount command to store the data in the host, so that we can persist the data:

docker run -d --name dev-postgres -e POSTGRES_PASSWORD=root -e PGDATA=/var/lib/postgresql/data/pgdata -v / custom/mount: / var/lib/postgresql/data - p, 6432:5432 postgres: 1.11Copy the code

If you’re not used to desktop visualization tools like Navicat, you can also use web-based tools like Pgadmin4

$ docker pull dpage/pgadmin4  
$ docker run    
    -p 80:80   
    -e '[email protected]'   
    -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret'   
    --name dev-pgadmin    
    -d dpage/pgadmin4
Copy the code

You can also use Python to interact with PostgrelSQL and install tripartite libraries:

pip3 isntall psycopg2
Copy the code
import psycopg2 import psycopg2.extras conn = psycopg2.connect(host='localhost', port=6432, user='postgres', password='root', database='mytest') cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute('SELECT * FROM article  WHERE id = 1; ') result = cursor.fetchone() print(result)Copy the code

You can query the data

Conclusion: If you are familiar with Mysql, PostgrelSQL is not a difficult thing to learn. From the moment Mysql was acquired by Oracle, it was not the best choice for open source software. So instead of stubbornly rejecting the zeitgeist, embrace the future and embrace PostgrelSQL.

The original article is reprinted from liu Yue’s Technology blog v3u.cn/a_id_171