式と関数

計算式の使用例

src/index.ts

//   SELECT で計算式に別名を付ける:
//     SELECT 出金額,
//            出金額 + 100 AS 百円増しの出金額
//       FROM 家計簿
//
//   INSERT 文で計算式を値として使う:
//     INSERT INTO 家計簿 (出金額)
//         VALUES (1000 + 105)
//
//   UPDATE 文で「列 + 100」の計算式を使う:
//     UPDATE 家計簿
//        SET 出金額 = 出金額 + 100

type KakeiboRow = { 日付: Date | null; 出金額: number }

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT 日付, 出金額
      FROM 家計簿
     ORDER BY 日付 NULLS LAST, 出金額
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(
    rows.map((r) => ({
      日付: r.日付 ? r.日付.toISOString().slice(0, 10) : null,
      出金額: r.出金額,
    })),
  )
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (日付 DATE, 出金額 INTEGER);`)

    await sql`INSERT INTO 家計簿 (日付, 出金額) VALUES ('2024-02-10',    0)`
    await sql`INSERT INTO 家計簿 (日付, 出金額) VALUES ('2024-02-11',  380)`
    await sql`INSERT INTO 家計簿 (日付, 出金額) VALUES ('2024-02-12', 2800)`
    await sql`INSERT INTO 家計簿 (日付, 出金額) VALUES ('2024-02-14', 5000)`
    await sql`INSERT INTO 家計簿 (日付, 出金額) VALUES ('2024-02-18', 7560)`

    await showKakeibo('初期データ')

    // ============================================================
    // 計算式に別名を付ける
    // ============================================================
    const aliased = await sql<{ 出金額: number; 百円増しの出金額: number }[]>`
      SELECT 出金額,
             出金額 + 100 AS 百円増しの出金額
        FROM 家計簿
       ORDER BY 出金額
    `
    console.log('\n--- SELECT で別名を付けた計算列 ---')
    console.table(aliased)

    // ============================================================
    // INSERT 文で計算式を値として使う
    //   1000 + 105 → 1105 が出金額に入る (日付は省略したので NULL)
    // ============================================================
    await sql`
      INSERT INTO 家計簿 (出金額)
           VALUES (1000 + 105)
    `
    await showKakeibo('1000 + 105 = 1105 を追加した後')

    // ============================================================
    // UPDATE 文で「列 + 100」を使う
    //   すべての行の 出金額 が +100 される
    // ============================================================
    await sql`
      UPDATE 家計簿
         SET 出金額 = 出金額 + 100
    `
    await showKakeibo('全行の 出金額 を +100 した後')
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- 初期データ (5件) ---
┌─────────┬──────────────┬────────┐
│ (index) │ 日付         │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0       │ '2024-02-10' │ 0      │
│ 1       │ '2024-02-11' │ 380    │
│ 2       │ '2024-02-12' │ 2800   │
│ 3       │ '2024-02-14' │ 5000   │
│ 4       │ '2024-02-18' │ 7560   │
└─────────┴──────────────┴────────┘

--- SELECT で別名を付けた計算列 ---
┌─────────┬────────┬──────────────────┐
│ (index) │ 出金額 │ 百円増しの出金額 │
├─────────┼────────┼──────────────────┤
│ 0       │ 0      │ 100              │
│ 1       │ 380    │ 480              │
│ 2       │ 2800   │ 2900             │
│ 3       │ 5000   │ 5100             │
│ 4       │ 7560   │ 7660             │
└─────────┴────────┴──────────────────┘

--- 1000 + 105 = 1105 を追加した後 (6件) ---
┌─────────┬──────────────┬────────┐
│ (index) │ 日付         │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0       │ '2024-02-10' │ 0      │
│ 1       │ '2024-02-11' │ 380    │
│ 2       │ '2024-02-12' │ 2800   │
│ 3       │ '2024-02-14' │ 5000   │
│ 4       │ '2024-02-18' │ 7560   │
│ 5       │ null         │ 1105   │
└─────────┴──────────────┴────────┘

--- 全行の 出金額 を +100 した後 (6件) ---
┌─────────┬──────────────┬────────┐
│ (index) │ 日付         │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0       │ '2024-02-10' │ 100    │
│ 1       │ '2024-02-11' │ 480    │
│ 2       │ '2024-02-12' │ 2900   │
│ 3       │ '2024-02-14' │ 5100   │
│ 4       │ '2024-02-18' │ 7660   │
│ 5       │ null         │ 1205   │
└─────────┴──────────────┴────────┘

CASE演算子 - 値を変換する

src/index.ts

// CASE 演算子で値を変換する例
//
//   /* 費目の値に応じて変換する */
//   SELECT 費目, 出金額,
//          CASE 費目 WHEN '居住費'     THEN '固定費'
//                    WHEN '水道光熱費' THEN '固定費'
//                    ELSE '変動費'
//          END AS 出費の分類
//     FROM 家計簿
//    WHERE 出金額 > 0

type KakeiboRow = { 費目: string; 出金額: number }

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT 費目, 出金額
      FROM 家計簿
     ORDER BY 出金額
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(rows)
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (費目 TEXT, 出金額 INTEGER);`)

    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('居住費',     85000)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('水道光熱費', 12000)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('食費',       38000)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('娯楽費',     15000)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('交通費',      6500)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('食費',           0)`

    await showKakeibo('家計簿の初期データ')

    // ============================================================
    // CASE 演算子で「費目」の値に応じて「固定費 / 変動費」に分類する
    //   居住費, 水道光熱費 → 固定費
    //   それ以外           → 変動費
    //   WHERE 出金額 > 0 で 0円の行は除外
    // ============================================================
    const classified = await sql<{ 費目: string; 出金額: number; 出費の分類: string }[]>`
      SELECT 費目, 出金額,
             CASE 費目 WHEN '居住費'     THEN '固定費'
                       WHEN '水道光熱費' THEN '固定費'
                       ELSE '変動費'
             END AS 出費の分類
        FROM 家計簿
       WHERE 出金額 > 0
       ORDER BY 出費の分類, 出金額 DESC
    `

    console.log('\n--- CASE で費目を「固定費 / 変動費」に変換した結果 ---')
    console.table(classified)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- 家計簿の初期データ (6件) ---
┌─────────┬──────────────┬────────┐
│ (index) │ 費目         │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0       │ '食費'       │ 0      │
│ 1       │ '交通費'     │ 6500   │
│ 2       │ '水道光熱費' │ 12000  │
│ 3       │ '娯楽費'     │ 15000  │
│ 4       │ '食費'       │ 38000  │
│ 5       │ '居住費'     │ 85000  │
└─────────┴──────────────┴────────┘

--- CASE で費目を「固定費 / 変動費」に変換した結果 ---
┌─────────┬──────────────┬────────┬────────────┐
│ (index) │ 費目         │ 出金額 │ 出費の分類 │
├─────────┼──────────────┼────────┼────────────┤
│ 0       │ '居住費'     │ 85000  │ '固定費'   │
│ 1       │ '水道光熱費' │ 12000  │ '固定費'   │
│ 2       │ '食費'       │ 38000  │ '変動費'   │
│ 3       │ '娯楽費'     │ 15000  │ '変動費'   │
│ 4       │ '交通費'     │ 6500   │ '変動費'   │
└─────────┴──────────────┴────────┴────────────┘

CASE演算子 - 条件に応じた値に変換する

src/index.ts

// CASE 演算子で条件に応じた値に変換する例
//
//   /* 条件に応じた値に変換する */
//   SELECT 費目, 入金額,
//          CASE WHEN 入金額 <   5000 THEN 'お小遣い'
//               WHEN 入金額 < 100000 THEN '一時収入'
//               WHEN 入金額 < 300000 THEN '給料出た！'
//               ELSE '想定外の収入です！'
//          END AS 収入の分類
//     FROM 家計簿
//    WHERE 入金額 > 0

type KakeiboRow = { 費目: string; 入金額: number }

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT 費目, 入金額
      FROM 家計簿
     ORDER BY 入金額
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(rows)
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (費目 TEXT, 入金額 INTEGER);`)

    await sql`INSERT INTO 家計簿 (費目, 入金額) VALUES ('お年玉',         3000)`
    await sql`INSERT INTO 家計簿 (費目, 入金額) VALUES ('臨時バイト代',  25000)`
    await sql`INSERT INTO 家計簿 (費目, 入金額) VALUES ('給料',         250000)`
    await sql`INSERT INTO 家計簿 (費目, 入金額) VALUES ('ボーナス',     500000)`
    await sql`INSERT INTO 家計簿 (費目, 入金額) VALUES ('食費',              0)`

    await showKakeibo('家計簿の初期データ')

    // ============================================================
    // CASE WHEN 条件 THEN 値 ... END の形 (検索 CASE)
    //   入金額の大小によって 'お小遣い' / '一時収入' / '給料出た！' /
    //   '想定外の収入です！' に分類する
    //   WHERE 入金額 > 0 で 入金のない行は除外
    // ============================================================
    const classified = await sql<{ 費目: string; 入金額: number; 収入の分類: string }[]>`
      SELECT 費目, 入金額,
             CASE WHEN 入金額 <   5000 THEN 'お小遣い'
                  WHEN 入金額 < 100000 THEN '一時収入'
                  WHEN 入金額 < 300000 THEN '給料出た！'
                  ELSE '想定外の収入です！'
             END AS 収入の分類
        FROM 家計簿
       WHERE 入金額 > 0
       ORDER BY 入金額
    `

    console.log('\n--- CASE WHEN 条件 ... で入金額を分類した結果 ---')
    console.table(classified)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- 家計簿の初期データ (5件) ---
┌─────────┬────────────────┬────────┐
│ (index) │ 費目           │ 入金額 │
├─────────┼────────────────┼────────┤
│ 0       │ '食費'         │ 0      │
│ 1       │ 'お年玉'       │ 3000   │
│ 2       │ '臨時バイト代' │ 25000  │
│ 3       │ '給料'         │ 250000 │
│ 4       │ 'ボーナス'     │ 500000 │
└─────────┴────────────────┴────────┘

--- CASE WHEN 条件 ... で入金額を分類した結果 ---
┌─────────┬────────────────┬────────┬──────────────────────┐
│ (index) │ 費目           │ 入金額 │ 収入の分類           │
├─────────┼────────────────┼────────┼──────────────────────┤
│ 0       │ 'お年玉'       │ 3000   │ 'お小遣い'           │
│ 1       │ '臨時バイト代' │ 25000  │ '一時収入'           │
│ 2       │ '給料'         │ 250000 │ '給料出た！'         │
│ 3       │ 'ボーナス'     │ 500000 │ '想定外の収入です！' │
└─────────┴────────────────┴────────┴──────────────────────┘

LENGTH / LEN

src/index.ts

// LENGTH 関数 と LEN 関数を使った例
//
// DBMS によって文字数を取る関数の名前が違う:
//   Oracle / PostgreSQL / MySQL / DB2 : LENGTH(...)
//   SQL Server                        : LEN(...)
//
// PostgreSQL には LEN は無いので、書籍と同じ書き味で試せるように
// LEN(text) を LENGTH(text) のラッパー関数として定義してから、
// LENGTH と LEN の戻り値が一致することを確認する。
//
//   SELECT メモ,
//          LENGTH(メモ) AS lengthの結果,
//          LEN(メモ)    AS lenの結果
//     FROM 家計簿

type KakeiboRow = { メモ: string }

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT メモ
      FROM 家計簿
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(rows)
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (メモ TEXT);`)

    // SQL Server の LEN を PostgreSQL でも使えるように、
    // LENGTH の薄いラッパー関数として LEN を作る
    await sql.unsafe(`
      CREATE OR REPLACE FUNCTION LEN(input TEXT)
      RETURNS INTEGER
      LANGUAGE sql
      IMMUTABLE
      AS $$
        SELECT LENGTH(input);
      $$;
    `)

    await sql`INSERT INTO 家計簿 (メモ) VALUES ('ラーメンを食べた')`
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('2月の家賃')`
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('文房具を購入')`
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('副業の報酬')`
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('週末カフェ代')`
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('Suicaチャージ')`

    await showKakeibo('家計簿の初期データ')

    // ============================================================
    // LENGTH と LEN を並べて呼び出し、戻り値が一致することを確認する
    //   PostgreSQL の LENGTH(TEXT) は文字数を返すので、
    //   ここで自作した LEN もそれに合わせて文字数を返す
    //   (cf. バイト数を取りたいときは OCTET_LENGTH を使う)
    // ============================================================
    const rows = await sql<
      {
        メモ: string
        lengthの結果: number
        lenの結果: number
        octet_lengthの結果: number
      }[]
    >`
      SELECT メモ,
             LENGTH(メモ)        AS lengthの結果,
             LEN(メモ)           AS lenの結果,
             OCTET_LENGTH(メモ)  AS octet_lengthの結果
        FROM 家計簿
       ORDER BY lengthの結果, メモ
    `

    console.log('\n--- LENGTH / LEN / OCTET_LENGTH を並べた結果 ---')
    console.table(rows)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- 家計簿の初期データ (6件) ---
┌─────────┬────────────────────┐
│ (index) │ メモ               │
├─────────┼────────────────────┤
│ 0       │ 'ラーメンを食べた' │
│ 1       │ '2月の家賃'        │
│ 2       │ '文房具を購入'     │
│ 3       │ '副業の報酬'       │
│ 4       │ '週末カフェ代'     │
│ 5       │ 'Suicaチャージ'    │
└─────────┴────────────────────┘

--- LENGTH / LEN / OCTET_LENGTH を並べた結果 ---
┌─────────┬────────────────────┬──────────────┬───────────┬────────────────────┐
│ (index) │ メモ               │ lengthの結果 │ lenの結果 │ octet_lengthの結果 │
├─────────┼────────────────────┼──────────────┼───────────┼────────────────────┤
│ 0       │ '2月の家賃'        │ 5            │ 5         │ 13                 │
│ 1       │ '副業の報酬'       │ 5            │ 5         │ 15                 │
│ 2       │ '文房具を購入'     │ 6            │ 6         │ 18                 │
│ 3       │ '週末カフェ代'     │ 6            │ 6         │ 18                 │
│ 4       │ 'ラーメンを食べた' │ 8            │ 8         │ 24                 │
│ 5       │ 'Suicaチャージ'    │ 9            │ 9         │ 17                 │
└─────────┴────────────────────┴──────────────┴───────────┴────────────────────┘

空白を除去する関数

src/index.ts

// TRIM / LTRIM / RTRIM で文字列の前後の空白を除去する例
//
//   TRIM(メモ)  → 左右の空白を除去
//   LTRIM(メモ) → 左の空白だけ除去
//   RTRIM(メモ) → 右の空白だけ除去

type KakeiboRow = { メモ: string }

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT メモ
      FROM 家計簿
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(
    rows.map((r) => ({
      'メモ (両端を | で囲って可視化)': `|${r.メモ}|`,
      文字数: r.メモ.length,
    })),
  )
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (メモ TEXT);`)

    await sql`INSERT INTO 家計簿 (メモ) VALUES ('  ラーメンを食べた  ')` // 左右に空白
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('   2月の家賃')` // 左にだけ空白
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('文房具を購入   ')` // 右にだけ空白
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('副業の報酬')` // 空白なし
    await sql`INSERT INTO 家計簿 (メモ) VALUES ('  Suica チャージ  ')` // 真ん中にも空白あり

    await showKakeibo('家計簿の初期データ (生の メモ)')

    // ============================================================
    // TRIM / LTRIM / RTRIM を並べて、効きの違いを確認する
    //   両端を | で囲んで結果を出すと、空白がどこまで残っているかが
    //   ひと目で分かる
    // ============================================================
    const trimmed = await sql<
      {
        元のメモ: string
        trim結果: string
        ltrim結果: string
        rtrim結果: string
        元の長さ: number
        trim後: number
        ltrim後: number
        rtrim後: number
      }[]
    >`
      SELECT '|' || メモ          || '|' AS 元のメモ,
             '|' || TRIM(メモ)    || '|' AS trim結果,
             '|' || LTRIM(メモ)   || '|' AS ltrim結果,
             '|' || RTRIM(メモ)   || '|' AS rtrim結果,
             LENGTH(メモ)        AS 元の長さ,
             LENGTH(TRIM(メモ))  AS trim後,
             LENGTH(LTRIM(メモ)) AS ltrim後,
             LENGTH(RTRIM(メモ)) AS rtrim後
        FROM 家計簿
       ORDER BY 元の長さ
    `

    console.log('\n--- TRIM / LTRIM / RTRIM を並べた結果 ---')
    console.table(trimmed)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- 家計簿の初期データ (生の メモ) (5件) ---
┌─────────┬────────────────────────────────┬────────┐
│ (index) │ メモ (両端を | で囲って可視化) │ 文字数 │
├─────────┼────────────────────────────────┼────────┤
│ 0       │ '|  ラーメンを食べた  |'       │ 12     │
│ 1       │ '|   2月の家賃|'               │ 8      │
│ 2       │ '|文房具を購入   |'            │ 9      │
│ 3       │ '|副業の報酬|'                 │ 5      │
│ 4       │ '|  Suica チャージ  |'         │ 14     │
└─────────┴────────────────────────────────┴────────┘

--- TRIM / LTRIM / RTRIM を並べた結果 ---
┌─────────┬──────────────────────────┬──────────────────────┬────────────────────────┬────────────────────────┬──────────┬────────┬─────────┬─────────┐
│ (index) │ 元のメモ                 │ trim結果             │ ltrim結果              │ rtrim結果              │ 元の長さ │ trim後 │ ltrim後 │ rtrim後 │
├─────────┼──────────────────────────┼──────────────────────┼────────────────────────┼────────────────────────┼──────────┼────────┼─────────┼─────────┤
│ 0       │ '|副業の報酬|'           │ '|副業の報酬|'       │ '|副業の報酬|'         │ '|副業の報酬|'         │ 5        │ 5      │ 5       │ 5       │
│ 1       │ '|   2月の家賃|'         │ '|2月の家賃|'        │ '|2月の家賃|'          │ '|   2月の家賃|'       │ 8        │ 5      │ 5       │ 8       │
│ 2       │ '|文房具を購入   |'      │ '|文房具を購入|'     │ '|文房具を購入   |'    │ '|文房具を購入|'       │ 9        │ 6      │ 9       │ 6       │
│ 3       │ '|  ラーメンを食べた  |' │ '|ラーメンを食べた|' │ '|ラーメンを食べた  |' │ '|  ラーメンを食べた|' │ 12       │ 8      │ 10      │ 10      │
│ 4       │ '|  Suica チャージ  |'   │ '|Suica チャージ|'   │ '|Suica チャージ  |'   │ '|  Suica チャージ|'   │ 14       │ 10     │ 12      │ 12      │
└─────────┴──────────────────────────┴──────────────────────┴────────────────────────┴────────────────────────┴──────────┴────────┴─────────┴─────────┘

REPLACE / SUBSTRING / SUBSTR / CONCAT

src/index.ts

// 文字列系の関数 REPLACE / SUBSTRING / SUBSTR / CONCAT の使用例
//
//   REPLACE(対象, 置換前, 置換後)
//     → 置換対象の文字列の中で「置換前」を全て「置換後」に置き換える
//
//   SUBSTRING(文字列, 開始位置, 文字数)
//   SUBSTR(文字列, 開始位置, 文字数)
//     → 文字列の一部を抜き出す (どちらが使えるかは DBMS による)
//
//   CONCAT(文字列, 文字列, ...)
//     → 複数の文字列を連結する

type KakeiboRow = { 費目: string; メモ: string }

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT 費目, メモ
      FROM 家計簿
     ORDER BY 費目, メモ
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(rows)
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (費目 TEXT, メモ TEXT);`)

    await sql`INSERT INTO 家計簿 (費目, メモ) VALUES ('食費',     'コーヒーを購入')`
    await sql`INSERT INTO 家計簿 (費目, メモ) VALUES ('娯楽費',   '書籍を購入')`
    await sql`INSERT INTO 家計簿 (費目, メモ) VALUES ('日用品費', '文房具を購入')`
    await sql`INSERT INTO 家計簿 (費目, メモ) VALUES ('交際費',   '同期会の会費')`
    await sql`INSERT INTO 家計簿 (費目, メモ) VALUES ('光熱費',   '1月の電気代')`

    await showKakeibo('家計簿の初期データ')

    // ============================================================
    // [1] REPLACE: メモに含まれる「購入」を全て「買った」に置き換える
    //     UPDATE 家計簿 SET メモ = REPLACE(メモ, '購入', '買った')
    // ============================================================
    await sql`
      UPDATE 家計簿
         SET メモ = REPLACE(メモ, '購入', '買った')
    `
    await showKakeibo('[1] REPLACE 実行後 (「購入」→「買った」)')

    // ============================================================
    // [2] SUBSTRING / SUBSTR: メモの先頭 3 文字だけを抜き出す
    //     SUBSTRING / SUBSTR は「何文字目から何文字分」を取り出す
    //     どちらの関数名でも同じ結果になる (PostgreSQL はどちらも使える)
    // ============================================================
    const sliced = await sql<
      {
        メモ: string
        substring結果: string
        substr結果: string
        先頭2文字以降: string
      }[]
    >`
      SELECT メモ,
             SUBSTRING(メモ, 1, 3) AS substring結果,
             SUBSTR(メモ,    1, 3) AS substr結果,
             SUBSTRING(メモ FROM 2) AS 先頭2文字以降
        FROM 家計簿
       ORDER BY メモ
    `
    console.log('\n--- [2] SUBSTRING / SUBSTR でメモの一部を抽出 ---')
    console.table(sliced)

    // ============================================================
    // [3] CONCAT: 費目とメモをコロン区切りでつなげて 1 つの文字列に
    //     SELECT CONCAT(費目, ':' || メモ) FROM 家計簿
    //     比較のために || 演算子だけで連結したものも並べる
    // ============================================================
    const concated = await sql<
      {
        費目: string
        メモ: string
        concat結果: string
        パイプ連結結果: string
      }[]
    >`
      SELECT 費目,
             メモ,
             CONCAT(費目, ':' || メモ) AS concat結果,
             費目 || ':' || メモ       AS パイプ連結結果
        FROM 家計簿
       ORDER BY 費目, メモ
    `
    console.log('\n--- [3] CONCAT と || で「費目:メモ」を作る ---')
    console.table(concated)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- 家計簿の初期データ (5件) ---
┌─────────┬────────────┬──────────────────┐
│ (index) │ 費目       │ メモ             │
├─────────┼────────────┼──────────────────┤
│ 0       │ '交際費'   │ '同期会の会費'   │
│ 1       │ '光熱費'   │ '1月の電気代'    │
│ 2       │ '娯楽費'   │ '書籍を購入'     │
│ 3       │ '日用品費' │ '文房具を購入'   │
│ 4       │ '食費'     │ 'コーヒーを購入' │
└─────────┴────────────┴──────────────────┘

--- [1] REPLACE 実行後 (「購入」→「買った」) (5件) ---
┌─────────┬────────────┬────────────────────┐
│ (index) │ 費目       │ メモ               │
├─────────┼────────────┼────────────────────┤
│ 0       │ '交際費'   │ '同期会の会費'     │
│ 1       │ '光熱費'   │ '1月の電気代'      │
│ 2       │ '娯楽費'   │ '書籍を買った'     │
│ 3       │ '日用品費' │ '文房具を買った'   │
│ 4       │ '食費'     │ 'コーヒーを買った' │
└─────────┴────────────┴────────────────────┘

--- [2] SUBSTRING / SUBSTR でメモの一部を抽出 ---
┌─────────┬────────────────────┬───────────────┬────────────┬──────────────────┐
│ (index) │ メモ               │ substring結果 │ substr結果 │ 先頭2文字以降    │
├─────────┼────────────────────┼───────────────┼────────────┼──────────────────┤
│ 0       │ '1月の電気代'      │ '1月の'       │ '1月の'    │ '月の電気代'     │
│ 1       │ 'コーヒーを買った' │ 'コーヒ'      │ 'コーヒ'   │ 'ーヒーを買った' │
│ 2       │ '同期会の会費'     │ '同期会'      │ '同期会'   │ '期会の会費'     │
│ 3       │ '文房具を買った'   │ '文房具'      │ '文房具'   │ '房具を買った'   │
│ 4       │ '書籍を買った'     │ '書籍を'      │ '書籍を'   │ '籍を買った'     │
└─────────┴────────────────────┴───────────────┴────────────┴──────────────────┘

--- [3] CONCAT と || で「費目:メモ」を作る ---
┌─────────┬────────────┬────────────────────┬───────────────────────────┬───────────────────────────┐
│ (index) │ 費目       │ メモ               │ concat結果                │ パイプ連結結果            │
├─────────┼────────────┼────────────────────┼───────────────────────────┼───────────────────────────┤
│ 0       │ '交際費'   │ '同期会の会費'     │ '交際費:同期会の会費'     │ '交際費:同期会の会費'     │
│ 1       │ '光熱費'   │ '1月の電気代'      │ '光熱費:1月の電気代'      │ '光熱費:1月の電気代'      │
│ 2       │ '娯楽費'   │ '書籍を買った'     │ '娯楽費:書籍を買った'     │ '娯楽費:書籍を買った'     │
│ 3       │ '日用品費' │ '文房具を買った'   │ '日用品費:文房具を買った' │ '日用品費:文房具を買った' │
│ 4       │ '食費'     │ 'コーヒーを買った' │ '食費:コーヒーを買った'   │ '食費:コーヒーを買った'   │
└─────────┴────────────┴────────────────────┴───────────────────────────┴───────────────────────────┘

ROUND / TRUNC / POWER

src/index.ts

// ROUND / TRUNC / POWER の動作を同じデータで一気に確認する例
//
//   ROUND(数値, 桁数) ... 指定桁で四捨五入
//   TRUNC(数値, 桁数) ... 指定桁で切り捨て
//   POWER(数値, 乗数) ... べき乗 (n 乗)
//
//   桁数の解釈は ROUND/TRUNC で共通:
//     桁数が正 → 小数部の桁数
//     桁数が負 → 整数部の桁数 (-1=10の位, -2=100の位, ...)
//
// データは「商品マスタ」を想定。税抜単価には 1 円未満の小数が出るのが
// 自然なので、整数値・小数値が混ざる現実的な値を入れている。

type ShouhinRow = { 商品名: string; 税抜単価: string }

async function showShouhin(label: string) {
  const rows = await sql<ShouhinRow[]>`
    SELECT 商品名, 税抜単価
      FROM 商品
     ORDER BY 税抜単価
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(rows)
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 商品;`)
    await sql.unsafe(`CREATE TABLE 商品 (商品名 TEXT, 税抜単価 NUMERIC);`)

    await sql`INSERT INTO 商品 (商品名, 税抜単価) VALUES ('ガム',              108)` // 整数
    await sql`INSERT INTO 商品 (商品名, 税抜単価) VALUES ('ボールペン',        165.5)` // .50 (中間値)
    await sql`INSERT INTO 商品 (商品名, 税抜単価) VALUES ('USB ケーブル',      980.49)` // .49 (中間未満)
    await sql`INSERT INTO 商品 (商品名, 税抜単価) VALUES ('コーヒー豆 200g', 1280.55)` // .55
    await sql`INSERT INTO 商品 (商品名, 税抜単価) VALUES ('お米 5kg',        2480.125)` // 小数 3 桁
    await sql`INSERT INTO 商品 (商品名, 税抜単価) VALUES ('ノートPC',       89800)` // 整数 (大きめ)
    await sql`INSERT INTO 商品 (商品名, 税抜単価) VALUES ('特売アメ',           0.1234)` // 1 円未満の小数

    await showShouhin('商品の初期データ')

    // ============================================================
    // [1] ROUND: 桁数 -2 〜 +2 で四捨五入
    // ============================================================
    const rounded = await sql<
      {
        商品名: string
        税抜単価: string
        '百円単位 (-2)': string
        '十円単位 (-1)': string
        '整数化 (0)': string
        '小数1桁 (1)': string
        '小数2桁 (2)': string
      }[]
    >`
      SELECT 商品名,
             税抜単価,
             ROUND(税抜単価, -2) AS "百円単位 (-2)",
             ROUND(税抜単価, -1) AS "十円単位 (-1)",
             ROUND(税抜単価,  0) AS "整数化 (0)",
             ROUND(税抜単価,  1) AS "小数1桁 (1)",
             ROUND(税抜単価,  2) AS "小数2桁 (2)"
        FROM 商品
       ORDER BY 税抜単価
    `
    console.log('\n--- [1] ROUND(税抜単価, 桁数) を -2 〜 +2 で並べた結果 (四捨五入) ---')
    console.table(rounded)

    // ============================================================
    // [2] TRUNC: 桁数 -2 〜 +2 で切り捨て (ROUND と同じ桁を並べる)
    // ============================================================
    const truncated = await sql<
      {
        商品名: string
        税抜単価: string
        '百円単位 (-2)': string
        '十円単位 (-1)': string
        '整数化 (0)': string
        '小数1桁 (1)': string
        '小数2桁 (2)': string
      }[]
    >`
      SELECT 商品名,
             税抜単価,
             TRUNC(税抜単価, -2) AS "百円単位 (-2)",
             TRUNC(税抜単価, -1) AS "十円単位 (-1)",
             TRUNC(税抜単価,  0) AS "整数化 (0)",
             TRUNC(税抜単価,  1) AS "小数1桁 (1)",
             TRUNC(税抜単価,  2) AS "小数2桁 (2)"
        FROM 商品
       ORDER BY 税抜単価
    `
    console.log('\n--- [2] TRUNC(税抜単価, 桁数) を -2 〜 +2 で並べた結果 (切り捨て) ---')
    console.table(truncated)

    // ============================================================
    // [3] POWER: 2 乗 / 3 乗
    //   POWER の結果は numeric の高精度で返るので、見やすさのため
    //   ROUND(..., 4) で小数 4 桁に丸めて表示する
    // ============================================================
    const powered = await sql<
      {
        商品名: string
        税抜単価: string
        '税抜単価^2': string
        '税抜単価^3': string
      }[]
    >`
      SELECT 商品名,
             税抜単価,
             ROUND(POWER(税抜単価, 2), 4) AS "税抜単価^2",
             ROUND(POWER(税抜単価, 3), 4) AS "税抜単価^3"
        FROM 商品
       ORDER BY 税抜単価
    `
    console.log('\n--- [3] POWER(税抜単価, n) で 2 乗 / 3 乗 (小数 4 桁に丸めて表示) ---')
    console.table(powered)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- 商品の初期データ (7件) ---
┌─────────┬───────────────────┬────────────┐
│ (index) │ 商品名            │ 税抜単価   │
├─────────┼───────────────────┼────────────┤
│ 0       │ '特売アメ'        │ '0.1234'   │
│ 1       │ 'ガム'            │ '108'      │
│ 2       │ 'ボールペン'      │ '165.5'    │
│ 3       │ 'USB ケーブル'    │ '980.49'   │
│ 4       │ 'コーヒー豆 200g' │ '1280.55'  │
│ 5       │ 'お米 5kg'        │ '2480.125' │
│ 6       │ 'ノートPC'        │ '89800'    │
└─────────┴───────────────────┴────────────┘

--- [1] ROUND(税抜単価, 桁数) を -2 〜 +2 で並べた結果 (四捨五入) ---
┌─────────┬───────────────────┬────────────┬───────────────┬───────────────┬────────────┬─────────────┬─────────────┐
│ (index) │ 商品名            │ 税抜単価   │ 百円単位 (-2) │ 十円単位 (-1) │ 整数化 (0) │ 小数1桁 (1) │ 小数2桁 (2) │
├─────────┼───────────────────┼────────────┼───────────────┼───────────────┼────────────┼─────────────┼─────────────┤
│ 0       │ '特売アメ'        │ '0.1234'   │ '0'           │ '0'           │ '0'        │ '0.1'       │ '0.12'      │
│ 1       │ 'ガム'            │ '108'      │ '100'         │ '110'         │ '108'      │ '108.0'     │ '108.00'    │
│ 2       │ 'ボールペン'      │ '165.5'    │ '200'         │ '170'         │ '166'      │ '165.5'     │ '165.50'    │
│ 3       │ 'USB ケーブル'    │ '980.49'   │ '1000'        │ '980'         │ '980'      │ '980.5'     │ '980.49'    │
│ 4       │ 'コーヒー豆 200g' │ '1280.55'  │ '1300'        │ '1280'        │ '1281'     │ '1280.6'    │ '1280.55'   │
│ 5       │ 'お米 5kg'        │ '2480.125' │ '2500'        │ '2480'        │ '2480'     │ '2480.1'    │ '2480.13'   │
│ 6       │ 'ノートPC'        │ '89800'    │ '89800'       │ '89800'       │ '89800'    │ '89800.0'   │ '89800.00'  │
└─────────┴───────────────────┴────────────┴───────────────┴───────────────┴────────────┴─────────────┴─────────────┘

--- [2] TRUNC(税抜単価, 桁数) を -2 〜 +2 で並べた結果 (切り捨て) ---
┌─────────┬───────────────────┬────────────┬───────────────┬───────────────┬────────────┬─────────────┬─────────────┐
│ (index) │ 商品名            │ 税抜単価   │ 百円単位 (-2) │ 十円単位 (-1) │ 整数化 (0) │ 小数1桁 (1) │ 小数2桁 (2) │
├─────────┼───────────────────┼────────────┼───────────────┼───────────────┼────────────┼─────────────┼─────────────┤
│ 0       │ '特売アメ'        │ '0.1234'   │ '0'           │ '0'           │ '0'        │ '0.1'       │ '0.12'      │
│ 1       │ 'ガム'            │ '108'      │ '100'         │ '100'         │ '108'      │ '108.0'     │ '108.00'    │
│ 2       │ 'ボールペン'      │ '165.5'    │ '100'         │ '160'         │ '165'      │ '165.5'     │ '165.50'    │
│ 3       │ 'USB ケーブル'    │ '980.49'   │ '900'         │ '980'         │ '980'      │ '980.4'     │ '980.49'    │
│ 4       │ 'コーヒー豆 200g' │ '1280.55'  │ '1200'        │ '1280'        │ '1280'     │ '1280.5'    │ '1280.55'   │
│ 5       │ 'お米 5kg'        │ '2480.125' │ '2400'        │ '2480'        │ '2480'     │ '2480.1'    │ '2480.12'   │
│ 6       │ 'ノートPC'        │ '89800'    │ '89800'       │ '89800'       │ '89800'    │ '89800.0'   │ '89800.00'  │
└─────────┴───────────────────┴────────────┴───────────────┴───────────────┴────────────┴─────────────┴─────────────┘

--- [3] POWER(税抜単価, n) で 2 乗 / 3 乗 (小数 4 桁に丸めて表示) ---
┌─────────┬───────────────────┬────────────┬───────────────────┬────────────────────────┐
│ (index) │ 商品名            │ 税抜単価   │ 税抜単価^2        │ 税抜単価^3             │
├─────────┼───────────────────┼────────────┼───────────────────┼────────────────────────┤
│ 0       │ '特売アメ'        │ '0.1234'   │ '0.0152'          │ '0.0019'               │
│ 1       │ 'ガム'            │ '108'      │ '11664.0000'      │ '1259712.0000'         │
│ 2       │ 'ボールペン'      │ '165.5'    │ '27390.2500'      │ '4533086.3750'         │
│ 3       │ 'USB ケーブル'    │ '980.49'   │ '961360.6401'     │ '942604494.0116'       │
│ 4       │ 'コーヒー豆 200g' │ '1280.55'  │ '1639808.3025'    │ '2099856521.7664'      │
│ 5       │ 'お米 5kg'        │ '2480.125' │ '6151020.0156'    │ '15255298516.2520'     │
│ 6       │ 'ノートPC'        │ '89800'    │ '8064040000.0000' │ '724150792000000.0000' │
└─────────┴───────────────────┴────────────┴───────────────────┴────────────────────────┘

日付

src/index.ts

// 現在の日時を得る関数 CURRENT_TIMESTAMP / CURRENT_DATE / CURRENT_TIME の例
//
//   CURRENT_TIMESTAMP  ⇒ 現在の日時 (年, 月, 日, 時, 分, 秒)
//   CURRENT_DATE       ⇒ 現在の日付 (年, 月, 日)
//   CURRENT_TIME       ⇒ 現在の時刻 (時, 分, 秒)
//
//   ※ 関数名の後ろに () は付けない (CURRENT_DATE() と書くとエラーになる DBMS が多い)
//
// 現在の日付を取得して登録する
//     INSERT INTO 家計簿
//          VALUES (CURRENT_DATE, '食費', 'ドーナツを買った', 0, 260)

type KakeiboRow = {
  日付: Date
  費目: string
  メモ: string
  入金額: number
  出金額: number
}

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT 日付, 費目, メモ, 入金額, 出金額
      FROM 家計簿
     ORDER BY 日付, 費目
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(
    rows.map((r) => ({
      日付: r.日付.toISOString().slice(0, 10),
      費目: r.費目,
      メモ: r.メモ,
      入金額: r.入金額,
      出金額: r.出金額,
    })),
  )
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`
      CREATE TABLE 家計簿 (
        日付   DATE,
        費目   TEXT,
        メモ   TEXT,
        入金額 INTEGER,
        出金額 INTEGER
      );
    `)

    // ============================================================
    // [0] 各関数の戻り値を SELECT で確認する
    //   () を付けないことに注意。CURRENT_DATE() などとは書けない
    // ============================================================
    const nowRows = await sql<
      {
        現在の日時: Date
        現在の日付: Date
        現在の時刻: string
      }[]
    >`
      SELECT CURRENT_TIMESTAMP AS 現在の日時,
             CURRENT_DATE      AS 現在の日付,
             CURRENT_TIME      AS 現在の時刻
    `
    console.log('\n--- [0] CURRENT_TIMESTAMP / CURRENT_DATE / CURRENT_TIME の戻り値 ---')
    console.table(
      nowRows.map((r) => ({
        現在の日時: r.現在の日時.toISOString(),
        現在の日付: r.現在の日付.toISOString().slice(0, 10),
        現在の時刻: r.現在の時刻,
      })),
    )

    // ============================================================
    // [1] CURRENT_DATE を使って今日の日付で INSERT
    //   INSERT INTO 家計簿
    //        VALUES (CURRENT_DATE, '食費', 'ドーナツを買った', 0, 260)
    // ============================================================
    await sql`
      INSERT INTO 家計簿
           VALUES (CURRENT_DATE, '食費', 'ドーナツを買った', 0, 260)
    `

    // ============================================================
    // [2] 別の費目もいくつか INSERT する。日付には CURRENT_DATE を使い回す
    //   どの行も「今日の日付」で記録される
    // ============================================================
    await sql`
      INSERT INTO 家計簿
           VALUES (CURRENT_DATE, '交通費',   'バス代',         0,  220)
    `
    await sql`
      INSERT INTO 家計簿
           VALUES (CURRENT_DATE, '日用品費', 'ティッシュ購入', 0,  398)
    `
    await sql`
      INSERT INTO 家計簿
           VALUES (CURRENT_DATE, '給与',     '副業の振込',   15000,   0)
    `

    await showKakeibo('[1][2] CURRENT_DATE を使って INSERT した家計簿')

    // ============================================================
    // [3] CURRENT_DATE を WHERE 句で使う
    //   今日の出費だけを抽出する。日付の比較に CURRENT_DATE が
    //   そのまま使えるので、「今日のぶん」を取り出すクエリが
    //   ハードコード不要で書ける
    // ============================================================
    const todayExpenses = await sql<{ 費目: string; メモ: string; 出金額: number }[]>`
      SELECT 費目, メモ, 出金額
        FROM 家計簿
       WHERE 日付 = CURRENT_DATE
         AND 出金額 > 0
       ORDER BY 出金額 DESC
    `
    console.log('\n--- [3] 今日 (CURRENT_DATE) の出費だけを抽出 ---')
    console.table(todayExpenses)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

--- [0] CURRENT_TIMESTAMP / CURRENT_DATE / CURRENT_TIME の戻り値 ---
┌─────────┬────────────────────────────┬──────────────┬──────────────────────┐
│ (index) │ 現在の日時                 │ 現在の日付   │ 現在の時刻           │
├─────────┼────────────────────────────┼──────────────┼──────────────────────┤
│ 0       │ '2026-05-23T06:01:24.072Z' │ '2026-05-23' │ '06:01:24.072545+00' │
└─────────┴────────────────────────────┴──────────────┴──────────────────────┘

--- [1][2] CURRENT_DATE を使って INSERT した家計簿 (4件) ---
┌─────────┬──────────────┬────────────┬────────────────────┬────────┬────────┐
│ (index) │ 日付         │ 費目       │ メモ               │ 入金額 │ 出金額 │
├─────────┼──────────────┼────────────┼────────────────────┼────────┼────────┤
│ 0       │ '2026-05-23' │ '交通費'   │ 'バス代'           │ 0      │ 220    │
│ 1       │ '2026-05-23' │ '日用品費' │ 'ティッシュ購入'   │ 0      │ 398    │
│ 2       │ '2026-05-23' │ '給与'     │ '副業の振込'       │ 15000  │ 0      │
│ 3       │ '2026-05-23' │ '食費'     │ 'ドーナツを買った' │ 0      │ 260    │
└─────────┴──────────────┴────────────┴────────────────────┴────────┴────────┘

--- [3] 今日 (CURRENT_DATE) の出費だけを抽出 ---
┌─────────┬────────────┬────────────────────┬────────┐
│ (index) │ 費目       │ メモ               │ 出金額 │
├─────────┼────────────┼────────────────────┼────────┤
│ 0       │ '日用品費' │ 'ティッシュ購入'   │ 398    │
│ 1       │ '食費'     │ 'ドーナツを買った' │ 260    │
│ 2       │ '交通費'   │ 'バス代'           │ 220    │
└─────────┴────────────┴────────────────────┴────────┘

:::

CAST - データ型を変換する

src/index.ts

// CAST 関数でデータ型を変換する例
//
//   CAST(変換する値 AS 変換する型) ⇒ 変換後の値
//
//   書籍の例 (SQL Server 風):
//     CAST(出金額 AS VARCHAR(20)) + '円'
//   PostgreSQL では文字列連結は + ではなく || なので、
//     CAST(出金額 AS VARCHAR(20)) || '円'
//   と書く。PostgreSQL にはさらに短縮形の  値::型  もある。

type KakeiboRow = { 費目: string; 出金額: number }

async function showKakeibo(label: string) {
  const rows = await sql<KakeiboRow[]>`
    SELECT 費目, 出金額
      FROM 家計簿
     ORDER BY 出金額
  `
  console.log(`\n--- ${label} (${rows.length}件) ---`)
  console.table(rows)
}

async function main() {
  try {
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (費目 TEXT, 出金額 INTEGER);`)

    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('交通費',    220)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('食費',     1280)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('日用品費', 3980)`
    await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('居住費',  85000)`

    await showKakeibo('家計簿の初期データ (出金額 は INTEGER 型)')

    // ============================================================
    // [1] 数値 → 文字列 への CAST
    //   CAST(出金額 AS VARCHAR(20)) || '円' で 「1280円」 を作る
    //   比較のため PostgreSQL 専用の 値::型 形式も並べる
    // ============================================================
    const stringified = await sql<
      {
        費目: string
        '出金額 (数値のまま)': number
        'CAST で連結': string
        ':: で連結': string
      }[]
    >`
      SELECT 費目,
             出金額 AS "出金額 (数値のまま)",
             CAST(出金額 AS VARCHAR(20)) || '円' AS "CAST で連結",
             出金額::VARCHAR(20) || '円'         AS ":: で連結"
        FROM 家計簿
       ORDER BY 出金額
    `
    console.log('\n--- [1] CAST(出金額 AS VARCHAR(20)) で「数値+文字列」連結 ---')
    console.table(stringified)

    // ============================================================
    // [2] 文字列 → 数値 への CAST (逆方向)
    //   数値として解釈できる文字列ならそのまま INTEGER になる
    //   さらに数値演算 (+ 100) もちゃんと数値計算として動く
    // ============================================================
    const numericFromString = await sql<
      {
        元の文字列: string
        元の型: string
        'INTEGER にキャスト': number
        '+ 100 した結果': number
      }[]
    >`
      SELECT '1000'                              AS 元の文字列,
             pg_typeof('1000'::text)::text       AS 元の型,
             CAST('1000' AS INTEGER)             AS "INTEGER にキャスト",
             CAST('1000' AS INTEGER) + 100       AS "+ 100 した結果"
    `
    console.log('\n--- [2] 文字列 → 数値 への CAST (成功する例) ---')
    console.table(numericFromString)

    // ============================================================
    // [3] 数値として解釈できない文字列を数値型へキャスト → エラー
    //   書籍の吹き出しにあった注意点を実演する
    //   'MINATO' は数値ではないので INTEGER にできない
    // ============================================================
    console.log('\n--- [3] 数値として解釈できない文字列を CAST → エラー ---')
    try {
      await sql`SELECT CAST('MINATO' AS INTEGER) AS これは失敗する`
      console.log('(なぜか成功してしまった)')
    } catch (e) {
      const err = e as { code?: string; message?: string }
      console.log(`✅ 期待どおりエラー: ${err.code ?? ''} ${err.message ?? String(e)}`)
    }
  } catch (e) {
    console.error('❌ 想定外のエラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

実行結果

// COALESCE 関数 ─ 最初に登場する NULL でない値を返す
//
//   COALESCE(列や式1, 列や式2, 列や式3 ...)
//     ⇒ 引数のうち、最初に現れた NULL でない引数
//
//   ※ 任意の数の引数を指定できる。全ての引数の型はそろえる必要がある
//   ※ 全ての引数が NULL のときは戻り値も NULL になる

async function main() {
  try {
    // ============================================================
    // [1] literal パターンを 1 つの SELECT で
    //     'A','B','C' → 'A'  (最初から NULL でない)
    //     NULL,'B','C' → 'B' (2 番目で初めて見つかる)
    //     NULL,'B',NULL → 'B' (途中の NULL は無視される)
    //     NULL,NULL,'C' → 'C' (3 番目で見つかる)
    //     NULL,NULL,NULL → NULL (全部 NULL なら NULL)
    //
    // 最後の引数だけ NULL::text と書いているのは PostgreSQL 固有の事情:
    //
    //   - 裸の NULL は PostgreSQL では「型未定 (unknown 型) の NULL」
    //   - COALESCE は「全引数の型が揃う必要がある」関数
    //   - 引数が全部 unknown だと型が決まらず
    //       ERROR: COALESCE types unknown and unknown cannot be matched
    //     になる
    //   - そこで NULL::text (= CAST(NULL AS text) の短縮形) で
    //     どれか 1 つに型を明示し、残りの NULL もその型に揃えてもらう
    //
    // 他の引数のどれかが 'A' などの文字列リテラルだと、そこから text 型と
    // 推論できるのでキャスト不要。今回は 3 つとも NULL なので必要になる。
    // ============================================================
    const literals = await sql<
      {
        ABC: string
        nullBC: string
        nullBNull: string
        nullNullC: string
        全部NULL: string | null
      }[]
    >`
      SELECT COALESCE('A',  'B',  'C')   AS "ABC",
             COALESCE(NULL, 'B',  'C')   AS "nullBC",
             COALESCE(NULL, 'B',  NULL)  AS "nullBNull",
             COALESCE(NULL, NULL, 'C')   AS "nullNullC",
             COALESCE(NULL::text, NULL,  NULL)  AS "全部NULL"
    `
    console.log('\n--- [1] COALESCE の基本動作 (literal) ---')
    console.table(literals)

    // ============================================================
    // [2] 数値列に NULL が混ざるテーブルで COALESCE(列, 0) を試す
    //     入金額 / 出金額 のように「未入力 = 0 として扱いたい」
    //     ケースで COALESCE が一番役に立つ
    // ============================================================
    await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
    await sql.unsafe(`CREATE TABLE 家計簿 (費目 TEXT, 入金額 INTEGER, 出金額 INTEGER);`)

    await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('給与',     250000, NULL)`
    await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('食費',       NULL,  1280)`
    await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('交通費',     NULL,   220)`
    await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('臨時収入',  10000, NULL)`
    await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('調整',       NULL,  NULL)`

    const rows = await sql<
      {
        費目: string
        入金額: number | null
        出金額: number | null
        '入金額 (NULL→0)': number
        '出金額 (NULL→0)': number
        差引: number
      }[]
    >`
      SELECT 費目,
             入金額,
             出金額,
             COALESCE(入金額, 0) AS "入金額 (NULL→0)",
             COALESCE(出金額, 0) AS "出金額 (NULL→0)",
             COALESCE(入金額, 0) - COALESCE(出金額, 0) AS 差引
        FROM 家計簿
       ORDER BY 費目
    `
    console.log('\n--- [2] COALESCE(数値列, 0) で NULL を 0 として扱う ---')
    console.table(rows)
  } catch (e) {
    console.error('❌ エラーが発生しました:', e)
  } finally {
    await sql.end()
  }
}

main()

:::

実行結果

--- [1] COALESCE の基本動作 (literal) ---
┌─────────┬─────┬────────┬───────────┬───────────┬──────────┐
│ (index) │ ABC │ nullBC │ nullBNull │ nullNullC │ 全部NULL │
├─────────┼─────┼────────┼───────────┼───────────┼──────────┤
│ 0       │ 'A' │ 'B'    │ 'B'       │ 'C'       │ null     │
└─────────┴─────┴────────┴───────────┴───────────┴──────────┘

--- [2] COALESCE(数値列, 0) で NULL を 0 として扱う ---
┌─────────┬────────────┬────────┬────────┬─────────────────┬─────────────────┬────────┐
│ (index) │ 費目       │ 入金額 │ 出金額 │ 入金額 (NULL→0) │ 出金額 (NULL→0) │ 差引   │
├─────────┼────────────┼────────┼────────┼─────────────────┼─────────────────┼────────┤
│ 0       │ '交通費'   │ null   │ 220    │ 0               │ 220             │ -220   │
│ 1       │ '給与'     │ 250000 │ null   │ 250000          │ 0               │ 250000 │
│ 2       │ '臨時収入' │ 10000  │ null   │ 10000           │ 0               │ 10000  │
│ 3       │ '調整'     │ null   │ null   │ 0               │ 0               │ 0      │
│ 4       │ '食費'     │ null   │ 1280   │ 0               │ 1280            │ -1280  │
└─────────┴────────────┴────────┴────────┴─────────────────┴─────────────────┴────────┘

問題例1

受験者ID	午前	午後1	午後2	論述	平均点
SW1046	86	(A)	68	91	80
SW1350	65	53	70	(B)	68
SW1877	(C)	59	56	36	56

現在登録されているデータをもとに、(A)〜(C) に当てはまる点数をそれぞれ受験者IDごとに計算して登録する

src/index.ts

await sql`
  UPDATE 試験結果
     SET 午後1 = (80*4) - (86+68+91)
   WHERE 受験者ID = 'SW1046'
`
await sql`
  UPDATE 試験結果
     SET 論述 = (68*4) - (65+53+70)
   WHERE 受験者ID = 'SW1350'
`
await sql`
  UPDATE 試験結果
     SET 午前 = (56*4) - (59+56+36)
   WHERE 受験者ID = 'SW1877'
`

この試験に合格するには、次の条件をすべて満たす必要がある。

(1) 午前の点数は60点以上であること (2) 午後1と午後2を合計した点数が120点以上であること (3) 論述の点数が、午前・午後1・午後2を合計した点数の3割以上であることこれらの条件のもとに、合格者の受験者IDを抽出する。ただし、列見出しは「合格者ID」とすること。

src/index.ts

const goukakusha = await sql<{ 合格者ID: string }[]>`
  SELECT 受験者ID AS 合格者ID
    FROM 試験結果
   WHERE 午前 >= 60
     AND 午後1 + 午後2 >= 120
     AND 0.3 * (午前 + 午後1 + 午後2) <= 論述
`

問題例2

メールアドレス	国名	性別	年齢
yamada@example.jp	NULL	D	51
suzuki@example.uk	NULL	C	26
takahashi@example.cn	NULL	C	35
tanaka@example.fr	NULL	D	43
ito@example.vn	NULL	D	22

メールアドレスの最後の2文字が国コードであることを利用して、国名を登録したい。国コードを日本語の国名に変換の上、国別名を更新する。ただし、1つのSQL文で全行を更新することなお、国コードと国名は次のように対応している。

jp:日本

uk:イギリス

cn:中国

fr:フランス

vn:ベトナム

om:日本

src/index.ts

await sql`
  UPDATE 回答者
     SET 国名 = CASE SUBSTRING(TRIM(メールアドレス),
                                LENGTH(TRIM(メールアドレス)) - 1, 2)
                  WHEN 'jp' THEN '日本'
                  WHEN 'uk' THEN 'イギリス'
                  WHEN 'cn' THEN '中国'
                  WHEN 'fr' THEN 'フランス'
                  WHEN 'vn' THEN 'ベトナム'
                  WHEN 'om' THEN '日本'
                END
`

メールアドレスと住居、年齢を一覧表示する。ただし、次の条件を満たした形で表示すること。

(1) メールアドレスの余分な空白は除去する (2)住居と年齢は1つの項目とし、見出しを「属性」とする。住居は「D」が戸建て、「C」が集合住宅を表している。年齢は年代として次のように表示する。ただし、20~50代のみ考慮すればよい。

src/index.ts

const ichiran = await sql<{ メールアドレス: string; 属性: string }[]>`
  SELECT TRIM(メールアドレス) AS メールアドレス,
         CASE WHEN 年齢 >= 20 AND 年齢 < 30 THEN '20代'
              WHEN 年齢 >= 30 AND 年齢 < 40 THEN '30代'
              WHEN 年齢 >= 40 AND 年齢 < 50 THEN '40代'
              WHEN 年齢 >= 50 AND 年齢 < 60 THEN '50代'
         END
         || ':' ||
         CASE 住居 WHEN 'D' THEN '戸建て'
                   WHEN 'C' THEN '集合住宅'
         END                                AS 属性
    FROM 回答者
   ORDER BY 年齢
`

問題例3

ある会社では、依頼された品物に刺繍で文字を入れるサービスを行なっている。加工にかかる金額は、1文字ごとに設定された金額を文字数で乗算したもので、1文字ごとの金額は刺繍する書体の種類に応じて決まる。また、10文字を超える場合は、一律500円の特別加工料が加算される。

受注日	受注ID	文字	文字数	書体コード
2023-12-05	101	Satou	NULL	2
2023-12-05	102	鈴木一郎	NULL	3
2023-12-05	113	野球 BASE CLUB	NULL	1
2023-12-08	140	N.R.	NULL	NULL

書体と単価

書体コード1: ブロック体単価: 100円
書体コード2: 明朝体　　単価: 150円
書体コード3: 草書体単価: 200円

※ 受注時に書体が指定されなかった場合は、書体コードにNULLが指定されるが、ブロック体による加工が適用される。

これらを元に、以下の設問で指示されたSQL文を作成する。なお、受注日列はDATE型、受注ID、文字、書体コード列はVARCHAR型、文字数列はINTEGER型で定義されている。

src/index.ts

await sql.unsafe(`
  CREATE TABLE 受注 (
    受注日       DATE,
    受注ID       VARCHAR(8) PRIMARY KEY,
    文字         VARCHAR(50),
    文字数       INTEGER,
    書体コード   VARCHAR(1)
  );
`

await sql`INSERT INTO 受注 VALUES ('2023-12-05', '101', 'Satou',          NULL, '2')`
await sql`INSERT INTO 受注 VALUES ('2023-12-05', '102', '鈴木 一郎',       NULL, '3')`
await sql`INSERT INTO 受注 VALUES ('2023-12-05', '113', '野球 BASE CLUB',  NULL, '1')`
await sql`INSERT INTO 受注 VALUES ('2023-12-08', '140', 'N.R.',            NULL, NULL)`

依頼された文字は、何文字の刺繍が必要かを求める。「文字」列のデータをもとに、1つのSQL文で「文字数」列の全行を更新する。ただし、「文字」列には半角の空白が入る可能性があるが、空白は文字数に含めない。なお、使用するDBMSでは、文字列長を得る関数はバイト数ではなく文字数を返すものとする。

src/index.ts

await sql`
  UPDATE 受注
     SET 文字数 = LENGTH(REPLACE(文字, ' ', ''))
`

受注内容を一覧表示する。一覧には、受注日、受注ID、文字数、書体名、単価、特別加工料を載せ、受注日および受注ID順に表示したい。ただし、特別加工料がかからないものについては、特別加工料をゼロとする。

src/index.ts

const ichiran = await sql<
  {
    受注日: Date
    受注ID: string
    文字数: number
    書体名: string
    単価: number
    特別加工料: number
  }[]
>`
  SELECT 受注日, 受注ID, 文字数,
         CASE COALESCE(書体コード, '1')
           WHEN '1' THEN 'ブロック体'
           WHEN '2' THEN '明朝体'
           WHEN '3' THEN '草書体'
         END                                 AS 書体名,
         CASE COALESCE(書体コード, '1')
           WHEN '1' THEN 100
           WHEN '2' THEN 150
           WHEN '3' THEN 200
         END                                 AS 単価,
         CASE WHEN 文字数 > 10 THEN 500
              ELSE 0
         END                                 AS 特別加工料
    FROM 受注
   ORDER BY 受注日, 受注ID
`

受注IDが113の注文に対して、文字の一部を変更したいという依頼があった。登録せれている文字を次の依頼内容に合わせて更新する。依頼内容: 半角スペースを「★」に変更

src/index.ts

await sql`
  UPDATE 受注
     SET 文字 = REPLACE(文字, ' ', '★')
   WHERE 受注ID = '113'
`

式と関数 ​

計算式の使用例 ​

実行結果 ​

CASE演算子 - 値を変換する ​

実行結果 ​

CASE演算子 - 条件に応じた値に変換する ​

実行結果 ​

LENGTH / LEN ​

実行結果 ​

空白を除去する関数 ​

実行結果 ​

REPLACE / SUBSTRING / SUBSTR / CONCAT ​

実行結果 ​

ROUND / TRUNC / POWER ​

実行結果 ​

日付 ​

実行結果 ​

CAST - データ型を変換する ​

実行結果 ​

実行結果 ​

問題例1 ​

問題例2 ​

問題例3 ​

書体と単価 ​

式と関数

計算式の使用例

実行結果

CASE演算子 - 値を変換する

実行結果

CASE演算子 - 条件に応じた値に変換する

実行結果

LENGTH / LEN

実行結果

空白を除去する関数

実行結果

REPLACE / SUBSTRING / SUBSTR / CONCAT

実行結果

ROUND / TRUNC / POWER

実行結果

日付

実行結果

CAST - データ型を変換する

実行結果

実行結果

問題例1

問題例2

問題例3

書体と単価