Useful PostgreSQL Tricks
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.
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 );
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.
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);
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.
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.
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!