The manual has it all. But in order to read and understand it entirely, you can spend years. Therefore, one of the most effective methods of teaching new Postgres features is to see how colleagues are doing. On specific examples. This article may be of interest to those who want to make deeper use of postgres capabilities or are considering switching to this DBMS.

Example 1

Suppose you need to get rows from a table that are not found in another exactly the same table, and with all fields checked for identity.
')
Traditionally it would be possible to write this (suppose, in table 3, the fields):

SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.field1 = t2.field1 AND t1.field2 = t2.field2 AND t1.field3 = t2.field3 WHERE t2.field1 IS NULL; 

Too verbose, in my opinion, and depends on the specific fields.
In the same way, you can use the Record type. You can get it from the table using the table name itself.

 postgres=# SELECT table1 FROM table1; table1 --------- (1,2,3) (2,3,4) 

(Output in parentheses)

Now finally filter the lines with identical fields.

 SELECT table1.* FROM table1 LEFT JOIN table2 ON table1 = table2 WHERE table2 Is NULL; 

or a bit more readable:

 SELECT * FROM table1 WHERE NOT EXISTS ( SELECT * FROM table2 WHERE table2 = table1 ); 

Example 2

Very vital task. A letter arrives, “Please insert such data for users 100, 110, 153, 100500”.
Those. You need to insert several lines, where id is different, and the rest is the same.
You can manually make such a “footcloth”:

 INSERT INTO important_user_table (id, date_added, status_id) VALUES (100, '2015-01-01', 3), (110, '2015-01-01', 3), (153, '2015-01-01', 3), (100500, '2015-01-01', 3); 

If id is a lot, it is slightly annoying. Also, I'm allergic to duplicate code.

To solve such problems, the posgrace has an “array” data type, as well as the unnest function, which makes data strings from an array.

for example

 postgres=# select unnest(array[1,2,3]) as id; id ---- 1 2 3 (3 rows) 

Those. in our example we can write like this

 INSERT INTO important_user_table (id, date_added, status_id) SELECT unnest(array[100, 110, 153, 100500]), '2015-01-01', 3; 

those. The id list is just copy-paste from the letter. Very comfortably.

By the way, if on the contrary you need an array from a query, then for this there is a function that is called so - array (). For example, select array (select id from important_user_table);

Example 3

For similar purposes, you can use another trick. Few people know the syntax

 VALUES (1, 'one'), (2, 'two'), (3, 'three') 

can be used not only in INSERT queries, but also in SELECT, you just need to take in parentheses

 SELECT * FROM ( VALUES (1, 'one'), (2, 'two'), (3, 'three') ) as t (digit_number, string_number); digit_number | string_number --------------+--------------- 1 | one 2 | two 3 | three (3 rows) 

Very convenient for handling pairs of values.

Example 4

Suppose you need something to insert, proapdeytit, and get the id of the affected elements. To do this, it is not necessary to do a lot of queries and create temporary tables. It is enough to cram it all into a CTE.

 WITH updated AS ( UPDATE table1 SET x = 5, y = 6 WHERE z > 7 RETURNING id ), inserted AS ( INSERT INTO table2 (x, y, z) VALUES (5, 7, 10) RETURNING id ) SELECT id FROM updated UNION SELECT id FROM inserted; 

But be very careful. All CTE subexpressions are executed in parallel with each other, and their sequence is not defined at all. Moreover, they use the same version (snapshot), i.e. if in one subexpression you add something to the table field, in another you subtract it, then it is possible that one of them will work.

Example 5

Suppose in some table called stats there is data for only one day:

 postgres=# select * from stats; added_at | money ------------+-------- 2016-04-04 | 100.00 (1 row) 

And you need to display the article for some period, replacing the missing data with zeros. This can be done using generate_series

 SELECT gs.added_at, coalesce(stats.money, 0.00) as money FROM generate_series('2016-04-01'::date, '2016-04-07'::date , interval '1 day') as gs(added_at) LEFT JOIN stats ON stats.added_at = gs.added_at; added_at | money ------------------------+-------- 2016-04-01 00:00:00+03 | 0.00 2016-04-02 00:00:00+03 | 0.00 2016-04-03 00:00:00+03 | 0.00 2016-04-04 00:00:00+03 | 100.00 2016-04-05 00:00:00+03 | 0.00 2016-04-06 00:00:00+03 | 0.00 2016-04-07 00:00:00+03 | 0.00 (7 rows) 

Of course, this trick works not only with dates, but also with numbers. And you can use several generate_series in one query:

 teasernet_maindb=> select generate_series (1,10), generate_series(1,2); generate_series | generate_series -----------------+----------------- 1 | 1 2 | 2 3 | 1 4 | 2 5 | 1 6 | 2 7 | 1 8 | 2 9 | 1 10 | 2 (10 rows) 

Example n + 1

In general, I write articles on Habr to get some new experience from the comments)
Please write what you use in your daily work. Something that is not possible for everyone is obvious, especially for people who have moved from other DBMS, for example, from the same mysql?