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

  • サーバー/接続のTimeZone設定を考慮して絶対的な時刻に正規化
  • オフセットなしの入力や出力表示時にタイムゾーンを考慮

timestamp

  • タイムゾーン変換なしで日時を保持
  • タイムゾーン情報を保持・正規化しない

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はその中にありません。

GEOSの今後のバージョンでTriangle OGC/WKTサポートが追加されると予測されているため、将来的にはPostgisにも含まれる可能性があります。(つまり、この答えは将来的に変更されるかもしれません)

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

  • 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. 

日時

PostgreSQLのtime精度上限を超える最小のtimestampリテラルはどれ？

CREATE TABLE class_sessions (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  start_time timestamptz,
  end_time timestamptz
);

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

  • 最大値: .123456（6桁）
  • ナノ秒（9桁）はサポートされた精度に丸められたり切り捨てられたりする
  • タイムゾーンオフセットは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...