DanLevy.net

クイズ: ディープPostgres パート1

SQLで悲鳴を上げますか?

第1部/第2部。第2部へ

PostgreSQL 🐘 は間違いなく私のお気に入りのデータベースです！常に新しい技や落とし穴について学び続けていますので、新しいクイズとしてまとめてみることにしました！

このクイズでは、よく知られたPostgreSQLの機能や、あまり知られていない落とし穴まで、ビルトイン集約関数から型キャスト、制約など、さまざまなトピックをカバーしています。

頑張ってください！🍀

1. 

ウォームアップ: 関数

PostgreSQLで組み込みの集約関数ではないものはどれ？

SELECT
  MIN(grade) as lowest,
  MAX(grade) as highest,
  AVG(grade) as average,
  MEDIAN(grade) as middle
FROM grades;

MEDIANは組み込み関数ではないよ！必要なら：

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

よく使われる組み込み集約関数：

  • MIN, MAX, COUNT
  • AVG, SUM
  • ARRAY_AGG, STRING_AGG
  • 統計関数など

2. 

ウォームアップ: 型変換

これらの中で無効な型変換❌はどれですか？

PostgreSQLでは3つの型変換構文がサポートされています：

  1. ANSI SQL: CAST(expression AS type)。
  2. PostgreSQL: expression::type。
  3. 型付きリテラル: type 'literal'。

すべて機能的には等価ですが：

  • CAST() は最もポータブルです。
  • :: はPostgreSQL特有ですがよく使われます。
  • 型付きリテラル形式の type 'literal' はあまり使われませんが依然有効です。

3. 

制約

ここではNULL値がいくつ許可されますか？

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255),
  UNIQUE(email)
);

PostgreSQLのUNIQUE制約:

  • 複数のNULL値を許可する。
  • 一意性チェックではNULL ≠ NULL。

NULL値を防ぐには、NOT NULLを追加する:

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255) NOT NULL,
  UNIQUE(email)
);

4. 

日時

これは何を返しますか？

SELECT '2024-11-27'::date + interval '24 hours';

Intervalは日付範囲操作を簡素化する強力なツールです！

PostgreSQLの日付演算:

  • + interval '24 hours' は24時間加算
  • + interval '1 day' は1日加算
  • + interval '1 month' は1ヶ月加算
  • + interval '1 year' は1年加算

結果は 2024-11-28 00:00:00 となります。

5. 

タイムスタンプ

「timestamptz」と「timestamp」について最も正確な説明はどれ？

どちらも8バイトですが、意味が異なります。

timestamptz は時刻をUTCの瞬間として保持し、元のタイムゾーン名やオフセットを保持しません。入力にオフセットがなければ接続の TimeZone を使い、出力もその設定に変換します。

timestamp はタイムゾーンを伴わない壁時計の日時です。入力のタイムゾーン指定を無視し、UTCへの変換は行いません。

めまいがするほど多彩な型

6. 

PostgreSQLのデータ型

これらの中で❌ PostgreSQLの有効なデータ型ではないものはどれ？

PostgreSQLには豊富なデータ型が存在しますが、STRING(100)はその中にありません。

有効な文字列型は以下の通りです：

  • VARCHAR(100)（変長文字列）
  • CHAR(100)（固定長文字列）
  • TEXT（無制限長）
  • CHARACTER VARYING(100)（VARCHAR(100)と同じ）

7. 

PostgreSQLデータ型

これらの中で、❌ PostgreSQLの有効なデータ型ではないものはどれですか？

decimal128はMongoDBやJavaなど他のシステムで使われる型ですが、PostgreSQLでは無効です。PostgreSQLで有効な数値型は以下です：

  • int（4バイト整数）
  • bigint（8バイト整数）
  • real（4バイト浮動小数点）
  • double precision（8バイト浮動小数点）
  • bigserial（8バイト自動増分整数）
  • smallserial（2バイト自動増分整数） decimal型はPostgreSQLで使用可能ですが、decimal128は存在しません。

8. 

PostgreSQLのデータ型

これらの中で、❌ 有効でないPostgreSQLのデータ型はどれですか？

これが原因で腹を立てたか？文句を言ったか？あなただけではありません！匿名の”コア”データベース貢献者が言いました：“おいダニエル、タイプの問題でクラッシュしちゃったぞ！それは攻撃的だ、先生！スコアは教えませんが、ははは。” 😈 どういたしまして。

PostgreSQLの豊富なネットワーク型にはipv4は含まれていません。毎回、ググらずに試みると間違えます。macaddr8があるせいで、ipv4やipv6型があると思いがちですが、実際はinetが両方をカバーします。cidrもネットマスクのIPv4/IPv6両方に対応しています。

有効なネットワーク型は以下の通りです：

  • cidr（IPv4/IPv6ネットワークアドレス）
  • inet（IPv4/IPv6ホストアドレス）
  • macaddr（MACアドレス）
  • macaddr8（EUI-64 MACアドレス）

9. 

PostgreSQL データ型

これらの中で有効なPostgreSQLタイプではないのはどれですか？❌

PostgreSQLには豊富な専用データ型がありますが、currencyはその中に含まれていません！

有効なタイプは以下の通りです：

  • xml（XMLデータ）
  • uuid（UUID）
  • money（通貨金額）
  • interval（時間間隔）

10. 

Postgresの型

これらの中で❌ PostgreSQLの有効な型でないものはどれ？

PostgreSQLには豊富な特殊型が存在しますが、triangleはその中にありません。

PostgreSQLの組み込み幾何型がこの問題の対象です。PostGISなどの拡張機能の対応は、triangle がPostgreSQLの組み込み型であることを意味しません。

正しい特殊型には以下が含まれます：

  • box (矩形)
  • line (無限直線)
  • point (2次元点)
  • circle (2次元円)
  • polygon (2次元ポリゴン)

11. 

整数演算

生徒IDの合計可能性を計算するとどうなりますか？

SELECT 256 * 256 * 256 * 256;

PostgreSQLのinteger型は32ビット符号付きで、-2,147,483,648から2,147,483,647の範囲を持ちます。

計算式256^4 = 4,294,967,296はこの範囲を超えています。

より大きな数値を扱うには:

-- Use BIGINT
SELECT 256::bigint * 256 * 256 * 256;


-- Or numeric for arbitrary precision
SELECT 256::numeric * 256 * 256 * 256;

12. 

日時

小数秒が6桁を超える選択肢のうち、小数秒の桁数が最も少ないタイムスタンプリテラルはどれですか？

PostgreSQLのタイムスタンプはマイクロ秒（6桁）の精度を持ちます。

  • 最大値: .123456（6桁）
  • 小数秒が6桁を超える入力は、保存できるマイクロ秒精度に丸められる
  • タイムゾーンオフセットはtimestamptzで受け入れられるが必須ではない

あまり知られていない落とし穴: 一部の言語/フレームワークはナノ秒精度で送信するが、PostgreSQLはマイクロ秒精度でタイムスタンプを保存する。

13. 

PostgreSQLタイプ

これらの中で❌無効なPostgreSQLタイプはどれですか？

（真剣に、これらは（主に）実在するタイプです。）

PostgreSQLには幾何学的およびテキスト検索用のタイプが組み込まれていますが、tsrankはその中に含まれません。

正しい幾何学的およびテキスト検索タイプは以下の通りです：

  • lseg（線分）
  • bytea（バイトデータ）
  • tsquery（テキスト検索クエリ）
  • tsvector（テキスト検索ドキュメント）

14. 

制約

この成績制約はいつ検証されますか？

ALTER TABLE students
ADD CONSTRAINT valid_grade
CHECK (
  (grade >= 0 AND grade <= 100) OR
  grade IS NULL
) NOT VALID;

NOT VALID制約:

  • 新規挿入や更新に対して即座に検証される
  • 既存の行は検証されない
  • 後でVALIDATE CONSTRAINTで既存行を検証可能
  • 大規模テーブルに便利

NOT VALIDなし:

  • 制約は即座に検証される
  • すべての既存行が検証される
  • 大規模テーブルでは遅くなる可能性がある

素晴らしい！PostgreSQLの複数の分野を深く掘り下げましたね🐘

何か新しいことを学んだり、少なくとも自慢できるスコアを手に入れられたりしたことを願っています！🏆

Part 2でさらにPostgresの楽しみをチェック！🚀Part 2

人生にさらにスリルを！私のクイズコレクションで無限に*楽しんでください！

Loading comments...