1. 型変換

PostgreSQLでは、::演算子を使用して値を特定の型に変換できます。たとえば、計算結果をnumeric型に変換するには以下のようにします：

( ... )::numeric(10, 2) -- 総桁数10、小数点以下2桁に変換

この例では、括弧内の計算結果を小数点以下2桁のnumeric型に変換しています。

2. COALESCE関数

COALESCE()関数は、複数の引数から最初のNULLでない値を返します。すべての引数がNULLの場合はNULLを返します。

SELECT COALESCE(name, nickname, '名無しさん') AS display_name FROM employees;

この例では、nameがNULLの場合、nicknameが使われ、それでもNULLであれば「名無しさん」と表示されます。

3. ILIKE演算子

PostgreSQLでは、ILIKEを使用して大文字小文字を区別しない文字列比較が可能です。

SELECT * FROM users WHERE username ILIKE '%test%';

このクエリは、usernameに「test」を含むレコードを大文字小文字問わず検索します。

4. JSONデータの抽出

JSON型のカラムから特定の値を抽出するには->>演算子を使用します。

SELECT details->>'email' AS user_email FROM user_profiles;

この例では、detailsカラムに格納されたJSONデータからemailフィールドを抽出しています。

5. ABS関数

ABS()関数は、数値の絶対値を返します。たとえば、未払い金額を判定するクエリ：

SELECT * FROM invoices WHERE ABS(total - paid) >= 0.01;

このクエリは、未払い金額が0.01以上あるレコードを抽出します。

6. TO_CHAR関数

日付を文字列に変換するにはTO_CHAR()を使用します。

SELECT TO_CHAR(order_date, 'YYYYMMDD') AS order_day FROM orders;

この例では、order_dateを「YYYYMMDD」形式の文字列に変換しています。

7. SUBSTRING関数

文字列の一部を抽出するにはSUBSTRING()を使用します。

SELECT SUBSTRING(product_code FROM 5) AS suffix FROM products;

この例では、product_codeの5文字目から最後までを抽出しています。

8. CONCAT関数

複数の文字列を結合するにはCONCAT()を使用します。

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

この例では、first_nameとlast_nameを結合してフルネームを表示しています。

9. JSONBがNULLでないレコードの検索

JSONB型のカラムが空でないレコードを検索する方法：

SELECT * FROM profiles WHERE JSONB_EXISTS(profile_data, 'address');

このクエリでは、profile_dataにaddressキーが含まれるレコードを抽出しています。

10. JSONBデータの解析

JSONBカラム内の配列やオブジェクトを操作する例：

SELECT info->0->'stock' AS stock_info FROM items WHERE info @> '[{"id": "1001"}]';

この例では、itemsテーブルのinfoカラムから最初の要素のstockフィールドを抽出しています。

11. ウィンドウ関数

ウィンドウ関数を使うことで、グループ内で計算しながら各行の情報を保持できます。

SELECT
    product_id,
    sale_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

この例では、product_idごとにsale_date順に番号を振っています。