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.

    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
        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;

    (Output in brackets)

    Now, finally, we filter the lines with identical fields

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

    or a little more readable:

    SELECT * 
    FROM table1 
        SELECT * 
        FROM table2 
            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)
    (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;
    (3 rows)

    Those. in our example, we can write like this

    INSERT INTO important_user_table 
    (id, date_added, status_id)
         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
        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.

    updated AS (
           UPDATE table1 
    	SET x = 5, y = 6
    	WHERE z > 7
            RETURNING id
    inserted AS (
           INSERT INTO table2
            (x, y, z)
            (5, 7, 10)
            RETURNING id
    SELECT id 
    FROM updated
    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(, 0.00) as money
        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!

    Also popular now: