バージョンごとのSQLコマンドの歴史

はじめに

にゃーん。
これはPostgreSQL Advent Calendar 2017の23日目の記事です。
昨日はMasahiko Sawadaさんに書いていただきました。

今日のお題

今日は技術的な話ではなく、PostgreSQLクロニクルみたいなネタです。たまにはこんな息抜きネタもいいよね？

PostgreSQLって結構たくさんのSQLコマンドがあるのですが、そういえば、「このSQLコマンドって、いつからサポートされているんだっけ？」みたいなことってありますよね？
今のPostgreSQL文書はこの辺がたどり易くはなっているのですが、さすがにバージョンごとのSQLコマンド一覧はないので、作ってみました。

バージョンごとのSQLコマンドサポート状況

つーか、表でかすぎ。

コマンド名	7.1	7.2	7.3	7.4	8.0	8.1	8.2	8.3	8.4	9.0	9.1	9.2	9.3	9.4	9.5	9.6	10	used?
ABORT	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER AGGREGATE				●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER COLLATION											●	●	●	●	●	●	●	なし
ALTER CONVERSION				●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
ALTER DATABASE			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER DEFAULT PRIVILEGES										●	●	●	●	●	●	●	●	なし
ALTER DOMAIN				●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER EVENT TRIGGER													●	●	●	●	●	あり
ALTER EXTENSION											●	●	●	●	●	●	●	あり
ALTER FOREIGN DATA WRAPPER									●	●	●	●	●	●	●	●	●	あり
ALTER FOREIGN TABLE											●	●	●	●	●	●	●	あり
ALTER FUNCTION				●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER GROUP	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
ALTER INDEX					●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER LANGUAGE				●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
ALTER LARGE OBJECT										●	●	●	●	●	●	●	●	なし
ALTER MATERIALIZED VIEW													●	●	●	●	●	あり
ALTER OPERATOR					●	●	●	●	●	●	●	●	●	●	●	●	●	なし
ALTER OPERATOR CLASS				●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
ALTER OPERATOR FAMILY								●	●	●	●	●	●	●	●	●	●	なし
ALTER POLICY															●	●	●	なし
ALTER PUBLICATION																	●	あり
ALTER ROLE						●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER RULE													●	●	●	●	●	なし
ALTER SCHEMA				●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER SEQUENCE				●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER SERVER									●	●	●	●	●	●	●	●	●	あり
ALTER STATISTICS																	●	なし
ALTER SUBSCRIPTION																	●	なし
ALTER SYSTEM														●	●	●	●	あり
ALTER TABLE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER TABLESPACE					●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER TEXT SEARCH CONFIGURATION								●	●	●	●	●	●	●	●	●	●	なし
ALTER TEXT SEARCH DICTIONARY								●	●	●	●	●	●	●	●	●	●	なし
ALTER TEXT SEARCH PARSER								●	●	●	●	●	●	●	●	●	●	なし
ALTER TEXT SEARCH TEMPLATE								●	●	●	●	●	●	●	●	●	●	なし
ALTER TRIGGER			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER TYPE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER USER					●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ALTER USER MAPPING									●	●	●	●	●	●	●	●	●	あり
ALTER VIEW	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ANALYZE		●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
BEGIN	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CHECKPOINT	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CLOSE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CLUSTER	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
COMMENT	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
COMMIT	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
COMMIT PREPARED						●	●	●	●	●	●	●	●	●	●	●	●	あり
COPY	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE ACCESS METHOD																●	●	なし
CREATE AGGREGATE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE CAST			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE COLLATION											●	●	●	●	●	●	●	なし
CREATE CONSTRAINT TRIGGER	●	●	●	●	●	●	●	●	●	●								なし
CREATE CONVERSION			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
CREATE DATABASE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE DOMAIN			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE EVENT TRIGGER													●	●	●	●	●	あり
CREATE EXTENSION											●	●	●	●	●	●	●	あり
CREATE FOREIGN DATA WRAPPER									●	●	●	●	●	●	●	●	●	あり
CREATE FOREIGN TABLE											●	●	●	●	●	●	●	あり
CREATE FUNCTION	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE GROUP	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE INDEX	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE LANGUAGE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE MATERIALIZED VIEW													●	●	●	●	●	あり
CREATE OPERATOR	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE OPERATOR CLASS			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE OPERATOR FAMILY								●	●	●	●	●	●	●	●	●	●	なし
CREATE POLICY															●	●	●	あり
CREATE PUBLICATION																	●	あり
CREATE ROLE						●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE RULE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE SCHEMA			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE SEQUENCE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE SERVER									●	●	●	●	●	●	●	●	●	あり
CREATE STATISTICS																	●	なし
CREATE SUBSCRIPTION																	●	あり
CREATE TABLE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE TABLE AS	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE TABLESPACE					●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE TEXT SEARCH CONFIGURATION								●	●	●	●	●	●	●	●	●	●	あり
CREATE TEXT SEARCH DICTIONARY								●	●	●	●	●	●	●	●	●	●	あり
CREATE TEXT SEARCH PARSER								●	●	●	●	●	●	●	●	●	●	あり
CREATE TEXT SEARCH TEMPLATE								●	●	●	●	●	●	●	●	●	●	あり
CREATE TRANSFORM															●	●	●	なし
CREATE TRIGGER	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE TYPE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE USER	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
CREATE USER MAPPING									●	●	●	●	●	●	●	●	●	あり
CREATE VIEW	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DEALLOCATE			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DECLARE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DELETE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DISCARD								●	●	●	●	●	●	●	●	●	●	あり
DO										●	●	●	●	●	●	●	●	あり
DROP ACCESS METHOD	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
DROP AGGREGATE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP CAST			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP COLLATION	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
DROP CONVERSION			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
DROP DATABASE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP DOMAIN			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP EVENT TRIGGER													●	●	●	●	●	あり
DROP EXTENSION											●	●	●	●	●	●	●	あり
DROP FOREIGN DATA WRAPPER	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP FOREIGN TABLE											●	●	●	●	●	●	●	あり
DROP FUNCTION	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP GROUP	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP INDEX	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP LANGUAGE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP MATERIALIZED VIEW													●	●	●	●	●	あり
DROP OPERATOR	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP OPERATOR CLASS			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP OPERATOR FAMILY								●	●	●	●	●	●	●	●	●	●	なし
DROP OWNED							●	●	●	●	●	●	●	●	●	●	●	なし
DROP POLICY															●	●	●	あり
DROP PUBLICATION																	●	あり
DROP ROLE						●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP RULE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP SCHEMA			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP SEQUENCE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP SERVER	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP STATISTICS																	●	なし
DROP SUBSCRIPTION																	●	あり
DROP TABLE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP TABLESPACE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP TEXT SEARCH CONFIGURATION								●	●	●	●	●	●	●	●	●	●	なし
DROP TEXT SEARCH DICTIONARY								●	●	●	●	●	●	●	●	●	●	なし
DROP TEXT SEARCH PARSER								●	●	●	●	●	●	●	●	●	●	なし
DROP TEXT SEARCH TEMPLATE								●	●	●	●	●	●	●	●	●	●	なし
DROP TRANSFORM															●	●	●	なし
DROP TRIGGER	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP TYPE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP USER	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP USER MAPPING	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
DROP VIEW	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
END	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
EXECUTE			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
EXPLAIN	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
FETCH	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
GRANT	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
IMPORT FOREIGN SCHEMA															●	●	●	あり
INSERT	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
LISTEN	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
LOAD	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
LOCK	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
MOVE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	なし
NOTIFY	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
PREPARE			●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
PREPARE TRANSACTION						●	●	●	●	●	●	●	●	●	●	●	●	あり
REASSIGN OWNED							●	●	●	●	●	●	●	●	●	●	●	あり
REFRESH MATERIALIZED VIEW													●	●	●	●	●	あり
REINDEX	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
RELEASE SAVEPOINT					●	●	●	●	●	●	●	●	●	●	●	●	●	あり
RESET	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
REVOKE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ROLLBACK	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
ROLLBACK PREPARED						●	●	●	●	●	●	●	●	●	●	●	●	あり
ROLLBACK TO SAVEPOINT					●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SAVEPOINT					●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SECURITY LABEL											●	●	●	●	●	●	●	なし
SELECT	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SELECT INTO	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SET	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SET CONSTRAINTS	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SET ROLE						●	●	●	●	●	●	●	●	●	●	●	●	あり
SET SESSION AUTHORIZATION		●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SET TRANSACTION	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
SHOW	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
START TRANSACTION	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
TRUNCATE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
UNLISTEN	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
UPDATE	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
VACUUM	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	●	あり
VALUES							●	●	●	●	●	●	●	●	●	●	●	あり

• お、こんなコマンドがこんな古いバージョンから存在していたのかー、とか
  • CREATE TYPEとかは、ほんと初期から存在していたんだねー。
  • 最初からLISTEN/NOTIFYがあるとは・・・
• このコマンドが存在しないときもあったのかー、とか
  • 7.1ではANALYZEコマンドなかったのね。
• 途中から削除されたSQLコマンドもあったのか！とか
  • CREATE CONSTRAINT TRIGGERコマンドは9.1から消えたっぽい。

まあ、そんなことを思いながら楽しく表をつくってました。
自分が使い出したのはPostgreSQL 7.4の頃からだけど、まだまだこの頃はいろんな機能がなかったんだなあと、しみじみ。

バージョンごとのSQLコマンド数

せっかくなのでバージョンごとのSQLコマンド数のグラフも無駄に作ってみた。

7.1のころから比べるとおよそ100個ほどSQLコマンドが増えているのだなあ。ちょい驚き。こんだけSQLコマンドがあると、結構使わないコマンドも多いんだよなあ。
特にEXTENSIONとかを作ったり組み込んだりしないと、まず使うことのないコマンドもPostgreSQLには多い。
で、さっきの表の右端に(used?)という列を入れて、自分が過去に使ったことがありそうなもの、なさそうなものを書いてみた。

自分の場合、拡張機能で遊んでみたり、実案件とは関係ない検証とかもするので、140個くらいのコマンドはたぶん使ったことはあるが、それでも残り40個くらいのコマンドは使ったことがない。
果たして今後、使う機会はあるのだろうか・・・

おわりに

明日はsho_kawaraさんの記事です。楽しみですね！