Useful tricks PostgreSQL

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


the

Example 1

Suppose we need to get the rows from table that are not in the other is exactly the same table and check all fields are the same.

Traditionally, one would write (say, in a table 3 fields):

the

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 wordy, in my opinion, and depends on the specific fields.
In posgres also possible to use a Record type. Get it from the table using the name table.

the

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

(Lead in brackets)

Now finally filter out rows with identical fields

the

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

or slightly more readable:

the

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

the

Example 2

Very vital task. A letter arrives “Put, please, for the users 100, 110, 153, 100500 such data”.
Ie we need to insert multiple rows where the id is different, and the rest is the same.
You can manually create a “puttee”:

the

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 a lot, it's a bit annoying. Besides, I'm allergic to code duplication.

To solve these problems in posgres is the data type of the array and unnest that array makes data rows.

For example,

the

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

I.e. in our example, we can write

the

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

i.e. the list id just copypaste. Very convenient.

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

the

Example 3

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

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

can be used not only INSERT queries, but to SELECT, it is only necessary to parenthesis to take
the

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 handling value pairs.

the

Example 4

Suppose you need something to insert, proupdate, and get the id of the affected items. To do this, you do not have to do a lot of queries and create temporary tables. Enough to cram all this in CTE.

the

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 of the subexpressions CTE vypolnyayutsya in parallel with each other, and their sequence is not fixed. Moreover, they use the same version (snapshot), i.e. if one sub-expression you added something to the table field, in the other subtracted, it is possible that work one of them.

the

Example 5

Let's say in a table called stats has data only for one day:

the

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

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

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 multiple generate_series in a single query:
the

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)

the

Example n+1

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

Article based on information from habrahabr.ru