マニュアルにはすべてがあります。 しかし、それを完全に読んで理解するには、何年もかかることがあります。 したがって、Postgresの新機能を教える最も効果的な方法の1つは、同僚の様子を確認することです。 特定の例について。 この記事は、postgresの機能をさらに活用したい人や、このDBMSへの移行を検討している人にとって興味深いものです。

例1

別のまったく同じテーブルにないテーブルから行を取得し、すべてのフィールドのIDをチェックするとします。



従来、次のように書くことができました（表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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

私の意見では、あまりにも冗長で、特定の分野に依存しています。

進行中は、レコードタイプを使用できます。 テーブル名自体を使用してテーブルから取得できます。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

（括弧内の出力）



最後に、同じフィールドを持つ行をフィルターで除外します。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

またはもう少し読みやすい：

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

例2

非常に重要なタスク。 「ユーザー100、110、153、100500などのデータを挿入してください」という手紙が届きます。

つまり idが異なり、残りは同じである複数の行を挿入する必要があります。

このような「フットクロス」を手動で作成できます。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

idが多い場合、これは少し面倒です。 さらに、私はコードの複製にアレルギーがあります。



進行中のこのような問題を解決するために、データ型「配列」と、配列からデータの行を作成するunnest関数があります。



例えば

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

つまり この例では、このように書くことができます

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

つまり IDリストはレターからコピーアンドペーストするだけです。 とても快適です。



ちなみに、逆にリクエストからの配列が必要な場合は、このために関数-array（）が必要です。 たとえば、select array（important_user_tableからidを選択）;

例3

同様の目的で、別のトリックを使用できます。 構文を知っている人は少ない

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

INSERTクエリだけでなくSELECTでも使用できます。括弧で囲む必要があるだけです

 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)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

値のペアを処理するのに非常に便利です。

例4

影響を受ける要素のIDを挿入、更新、取得する必要があるとします。 これを行うには、多くのクエリを作成して一時テーブルを作成する必要はありません。 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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

しかし、非常に注意してください。 すべてのCTE部分式は互いに並行して実行され、それらのシーケンスはまったく定義されていません。 さらに、同じバージョン（スナップショット）を使用します。 1つの部分式でテーブルフィールドに何かを追加し、別の部分式で減算すると、そのうちの1つが機能する可能性があります。

例5

statsと呼ばれる表に、1日だけのデータがあるとしましょう。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、ある期間にわたって像を表示し、欠落しているデータをゼロに置き換える必要があります。 これは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)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

もちろん、このトリックは日付だけでなく数字でも機能します。 また、1つのリクエストで複数のgenerate_seriesを使用できます。

 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)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

例n + 1

一般に、コメントから少し新しい経験を得るためにHabrに関する記事を書きます）

毎日の仕事で使用するものを書いてください。 特に同じmysqlからなど、他のDBMSから移動した人にとっては特に、誰にも明らかではないことはありますか？



Zinc Prod開発ポッドキャストを購読してください。ここでは、データベース、プログラミング言語、その他すべてについて議論しています。