2015 15th Sunday PostgreSQL Learning

English version of the website address: http://www.postgresql.org/

The latest version information shown above is PostgreSQL 9.4.1, 9.3.6, 9.2.10, 9.1.15 & 9.0.19 released! Chinese Community address: http://www.postgres.cn/home display2014/12/18: The Global Development team announces the release of the industry-leading open source relational database version PostgreSQL9.4, which adds a number of features to accommodate different database user flexibility, scalability, and performance improvement requirements, including support improvements to JSON data , which enhances the performance of replication and indexing .

• Function
  • PostgreSQL has the most features of large commercial databases, such as transactions, subqueries, triggers, views, foreign key referential integrity, and complex locking. We also have features that they don't have, such as user-defined types, inheritance, rules, and multi-version parallel controls to reduce lock contention, and so on.

• Performance
  • PostgreSQL and other commercial or open-source databases have similar performance. Some data processing is faster, some of the other processing is slower. Our performance is typically between +/-10% compared to other databases.

• Reliability
  • We all know that the database must be reliable, otherwise it will not be used at all. We strive to publish a carefully tested, least-flawed stable code. Each version has at least one months of beta testing time, and our release history shows that we can provide a stable, robust version that can be used for production use. We believe we are comparable to other database software in this respect.

• Support
  • Our mailing list provides a very large group of developers and users to help solve any problems encountered. We cannot guarantee that we will always be able to solve problems, in contrast, commercial database software is not always able to provide solutions. Direct contact with developers, user groups, manuals, and source programs makes PostgreSQL's support better than other databases. There are also commercial, comprehensive technical support that can be provided to those who need it. (see section 1.7)

• Price
  • We are free for any purpose, including commercial and non-commercial purposes. You may add our code to your product without limitation, except for the BSD copyrights stated in the above copyright notice.  

There are three main areas that can improve the potential of PostgreSQL.

• The change in query mode, which mainly involves modifying the query mode to get better performance:

• create indexes, including expressions and partial indexes;
• Use the copy statement instead of multiple INSERT statements;
• a transaction is composed of multiple SQL statements to reduce the cost of committing the transaction;
• use cluster when extracting multiple records from an index;
• use pre-compiled queries (Prepared query),
• use analyze to maintain accurate optimization statistics;
• use VACUUM or pg_autovacuum

• Configuration of the server
  • Many of the settings in profile postgres.conf can affect performance, and the list of all parameters is visible: Administrator's Guide/database server run environment/database server Run Configuration, explanation of parameters is visible: http://www.varlena.com/varlena/ Generalbits/tidbits/annotated_conf_e.html and http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.

• Choice of hardware
  • Effects of computer hardware on performance Browse http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html and/http www.powerpostgresql.com/PerfList/.

compared to mysql,postgresql and there is no rigid shortcomings, but its pure free features are really not comparable to MySQL. It can be a very important part of software product solutions, especially for many low-end users, not only in the use of no differences, but also to save a portion of the additional costs, which is very helpful for product marketing.
Study Manual: Http://www.cnblogs.com/stephen-liu74/archive/2012/06/08/2315679.htmlTurn multiple columns into 1 columns

This is often the case in practical applications, as shown in the table below, where we now need to count the sum of each person's wages and the month of issuance.

YearMoney

user_name		Month
Tom	2011	1	900
Tom	2011	2	1200
Tom	2011	5	1100
Tom	2011	6	1300
John doe	2011	1	1100
John doe	2011	3	1200

That we're going to get the results from the table below

YearTotal Money

user_name		monthes
Tom	2011	1,2,5,6	4500
John doe	2011	1,3	2300

The first thing we think about is that there are SQL statements like this:
SELECT user_name,year,myfunction (month) as Monthes, SUM (money) as Total_money from table GROUP by User_name,year;

Like count or sum, a field in the datasheet is stitched together as needed, but there seems to be no direct way to implement it (perhaps the author did not find it, because there is no discovery so the author studied half a day to get the following experience).

Method One: In PostgreSQL if you do not customize the function directly with SQL to implement

Select T1.user_name, T1.year, Array_to_string (ARRAY (SELECT t2.month from table T2 WHERE t2.user_name=t1.user_name and T2. year=t1.year), ', ') as Monthes, SUM (T1.money) as Total_money from table T1 WHERE 1 GROUP by T1.user_name, T1.year;

The main purpose of this is to use PostgreSQL's built-in array function array_to_string to stitch arrays into strings.

The Array_agg function of PostgreSQL is used to concatenate input values into an array, including an empty

SELECT ARRAY_AGG (SALARY) FROM COMPANY; result {20000,65000,85000,45000,10000}
SELECT UNNSET (ARRAY_AGG (SALARY)) FROM COMPANY function can restore the result to multiple rows

2015 15th Sunday PostgreSQL Learning