📘

postgreSQLのアウトプット

に公開
PostgreSQL
SQL
tech

よくわかっていなかったこと

PL/SQLとPostgreSQLは別物

PL/SQL

  • Oracle Databaseの手続き型拡張言語
  • Oracleが開発・所有している独自の言語
  • SQLに手続き型プログラミング機能（変数、条件分岐、ループなど）を追加
  • Oracle Databaseでのみ使用可能

PostgreSQL

  • オープンソースのリレーショナルデータベース管理システム（RDBMS）
  • データベースエンジン自体
  • SQL標準に準拠しており、多くの拡張機能を持つ
  • 手続き型言語としてPL/pgSQLを提供（PL/SQLとは似ているが別の言語）

違いのまとめ

項目 PL/SQL PostgreSQL
種類 プログラミング言語 データベースシステム
開発元 Oracle PostgreSQL Global Development Group
ライセンス 商用（Oracle） オープンソース
互換性 Oracle専用 標準SQLベース

pgAdminとは？

  • PostgreSQL専用の管理ツール
  • Webベースのグラフィカルユーザーインターface（GUI）
  • データベースの管理、監視、開発を行うための統合環境

類似ツール

  • Oracle: Oracle SQL Developer
  • MySQL: MySQL Workbench
  • SQL Server: SQL Server Management Studio (SSMS)

型について

日付・時刻データ型

データ型 説明 格納範囲 精度 サイズ 特徴
DATE 日付のみ 4713 BC ～ 5874897 AD 1日 4バイト 年月日のみ、時刻情報なし
TIME 時刻のみ 00:00:00 ～ 24:00:00 1マイクロ秒 8バイト 時分秒のみ、日付情報なし
TIME WITH TIME ZONE タイムゾーン付き時刻 00:00:00+1459 ～ 24:00:00-1459 1マイクロ秒 12バイト タイムゾーン情報を含む時刻
TIMESTAMP 日付と時刻 4713 BC ～ 294276 AD 1マイクロ秒 8バイト 日付と時刻の組み合わせ
TIMESTAMP WITH TIME ZONE タイムゾーン付き日時 4713 BC ～ 294276 AD 1マイクロ秒 8バイト タイムゾーン情報を含む日時
INTERVAL 時間間隔 -178000000年 ～ 178000000年 1マイクロ秒 16バイト 期間や時間差を表現

4713BC～5874897AD＝紀元前4713年～西暦5874897年の意味

-- DATE型
INSERT INTO events (event_date) VALUES ('2025-11-01');

-- TIME型
INSERT INTO schedule (start_time) VALUES ('14:30:00');

-- TIMESTAMP型
INSERT INTO logs (created_at) VALUES ('2025-11-01 14:30:00');

-- TIMESTAMP WITH TIME ZONE型
INSERT INTO logs (created_at) VALUES ('2025-11-01 14:30:00+09:00');

-- INTERVAL型
SELECT NOW() + INTERVAL '7 days';
SELECT AGE('2025-01-01', '2024-01-01'); -- 1年の間隔

タイムゾーンとは？

地球上の各地域ごとに定められた標準時刻の区分のこと。

詳しい説明
  • 地球は経度ごとに時刻が異なるため、世界をいくつかの地域（ゾーン）に分け、それぞれに標準時を定める。
  • 例：日本標準時 JST、協定世界時 UTC、アメリカ東部標準時 ESTなど
  • 各タイムゾーンは、協定世界時(UTC)を基準に「+9:00」や「-5:00」などの時差で表す。
  • 例えば、日本（JST）はUTC+9、ニューヨーク（EST）はUTC-5。UTC自体の場合は+00
主な用途
  • グローバルなシステムやアプリケーションで、異なる地域の時刻を正しく扱うために必要
  • データベースやプログラムで日時を保存・表示する際、タイムゾーンを考慮することで時刻のズレや誤解を防ぐ
具体例
-- 日本時間（JST）
2025-10-31 11:22:21.425955+09

-- アメリカ東部_冬時間（EST）
2025-10-30 22:22:21.425955-05

-- アメリカ東部_夏時間/サマータイム（EDT）
2025-10-30 22:22:21.425955-04

-- UTC（協定世界時）
2025-10-31 02:22:21.425955+00
注意点
  • サマータイム（DST）を導入している地域では、季節によってタイムゾーンが変わることがある
  • データベースやプログラムで日時を扱う際は、タイムゾーンの指定・変換が重要。

TIMESTAMP が適している場面

  • ログのタイムスタンプ（サーバーの時刻として記録）
  • 予定の開始時刻（特定の地域での時刻）
  • システム内部の処理時刻

TIMESTAMP WITH TIME ZONE が適している場面

  • グローバルなイベント時刻
  • ユーザーが異なるタイムゾーンにいる場合
  • 正確な時刻比較が必要な場合
  • PostgreSQLは内部的にUTCで保存

SELECT文での出力方法について

タイムゾーンを変更する

timestamp with timezone型のcreateDateを日本時間のtimestampに変更する。

SELECT
	createdate,
	(createdate AT TIME ZONE 'JST')::timestamp AS createdate_JST
FROM user;

--- createdate createdate_jst
--- 2025-10-31 09:17:05.836537+00 2025-10-31 18:17:05.836537"
--- 2025-10-31 08:05:05.381461+00 2025-10-31 17:05:05.381461

1行出力

-- とにかく1行を表示する（どの行が出るかはわからない）
SELECT createdate FROM user LIMIT 1;

-- 最新のレコード1件
SELECT createdate FROM user ORDER BY createdate DESC LIMIT 1;

データ削除について

テーブルの中身を全部消す

TRUNCATE TABLE user;

シーケンスをリセットする

データを削除した後、自動採番されるidなどをリセットしたいときに使う。

ALTER SEQUENCE user_id_seq RESTART WITH 1;

Discussion