The manual has it all. But to read it fully and to realize it can take years. Therefore, one of the most effective methods of teaching Postgres new 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 the capabilities of postgres or are considering the transition to this DBMS.

Example 1

Suppose you want to get rows from a table that aren’t in another exactly the same table, and with all the fields checked for identity.

Traditionally, one could write like this (suppose the fields in table 3):

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 specific fields.
You can use the Record type in progress. 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 brackets)

Now, finally, we filter the lines with identical fields

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

or a little more readable:

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

Example 2

A very vital task. A letter comes in, “Please insert such data for users 100, 110, 153, 100500.”
Those. you need to insert several lines, where id are 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, then this is slightly annoying. In addition, I am allergic to code duplication.

To solve such problems in progress there is the data type “array”, as well as the unnest function, which makes lines of data from the array.

for instance

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 the request, then for this there is a function, which 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 brackets

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)

It is very convenient for processing pairs of values.

Example 4

Suppose you need to insert, update, and get the id of the affected elements. To do this, it is not necessary to make many queries and create temporary tables. It’s enough to cram it all into the 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 added something to the table field, in another subtracted, then it is possible that one of them will work.

Example 5

Let's say 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 statue 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 request:

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 a little new experience from comments)
Please write that you use in daily work. Is there anything that is not obvious to everyone, especially for people who have moved from other DBMSs, for example, from the same mysql?

Subscribe to the Zinc Prod development podcast , where we discuss databases, programming languages, and everything else!