DDL(Data Definition Language)
DDLはデータ定義言語で、データベース(Database)・テーブル(Table)・インデックス(Index)・ロール(Role)・ビュー(View)などの作成(CREATE)・変更(ALTER)・削除(DROP)を行います。
オプションについては一部記載していますので、詳細はPostgreSQLのマニュアルを参照してください。
ユーザ
PostgreSQLで使用するユーザーを管理します。
PostgreSQLをインストールすると、「postgres」ユーザーが既に存在しています。
このサイトではそのユーザーを使用しますが、ユーザーを作成したり変更するなど管理する場合は「CREATE USER」や「ALTER USER」や「DROP USER」コマンドを使用します。
-
(1) 作成(CREATE)
- ・書式：CREATE USER ユーザ名 [オプション]
- ・説明：「CREATE USER」で新しいユーザを作成します。
-
[オプション]
- password：パスワードを設定します
- groupname：所属グループを設定します。
-
(2) 変更(ALTER)
- ・書式：ALTER USER ユーザ名 [オプション]
- ・説明：作成したユーザを変更します。
-
[オプション]
- password：パスワードを設定します
- newname：新しい名前に変更します。
- groupname：所属グループを設定します。
-
(3) 削除(DROP)
- ・書式：DROP USER ユーザ名
- ・説明：作成したユーザを削除します。
本サイトでは、「postgres」ユーザーをそのまま利用します。
そのため、ALTERでパスワードのみ変更します。
!sudo -u postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'postgres';"
データベース
PostgreSQLで使用するデータベースを管理します。
PostgresSQLでは、新規・既存のデータベースをテンプレート化してコピーすることができます。
データベース作成時に共通で使用したい関数などがあり、それを使用する場合にはテンプレート化します。
テンプレートには、template0とtemplate1があり、デフォルトではtemplate1がテンプレートデータベースとして作成され、追加した関数などが存在すれば、データベース作成時に組み込まれます。
共通のものを使用せずにクリーンな状態でデータベースを作成したいときにはtemplate0を指定してデータベースを作成します。
-
(1) 作成(CREATE)
- ・書式：CREATE DATABASE データベース名 [オプション]
- ・説明：「CREATE DATABASE」で新しいデータベースを作成します。データベースを作成するには、スーパーユーザ・CREATEDBの権限を持つロールである必要があります。
-
(2) 変更(ALTER)
- ・書式：ALTER DATABASE データベース名 [オプション]
- ・説明：「ALTER DATABASE」で作成したデータベースを変更します。データベースを変更するには、スーパーユーザ・CREATEDBの権限を持つロールである必要があります。
-
[オプション]
- CONNECTION LIMIT [同時接続数]：同時接続数を制限します。
- RENAME TO [新しいテーブル名]：新しい名前に変更します。
- OWNER TO [新しいオーナ名]：新しいオーナーに変更します
- SET TABLESPACE [テーブルスペース名]：テーブルスペースを設定します。
- SET [パラメータ名] { TO | = } { value | DEFAULT }：パラメータを設定します。
- RESET [パラメータ名]：パラメータをリセットします。
- RESET ALL：パラメータを初期化します。
-
(3) 削除(DROP)
- ・書式：DROP DATABASE データベース名
- ・説明：「DROP DATABASE」で作成したデータベースを削除します。所有者しているロールである必要があります。
本サイトでは、「quiz」データベースを作成します。
!sudo -u postgres psql -U postgres -c 'CREATE DATABASE quiz;'
テーブル
-
(1) 作成(CREATE)
- ・書式：CREATE TABLE スキーマ名.テーブル名 (カラム名 データ型 [カラム制約定義],・・・,・・・,[テーブル制約定義])
- ・説明：「CREATE TABLE」で新しいテーブルを作成します。デフォルトではデータベースに接続しているロールの所有として作成されます。テーブル名指定後は属性をカンマで区切りながら必要な属性分指定してきます。テーブル名のスキーマ名を省略するとpublicスキーマで作成されます。外部キーを参照する場合は、データ型の後にreferences テーブル名(列名)で設定します。 外部キーを指定する場合、CASCADEまたはRESTRICTが指定できます。CASCADEは外部キーを参照して更新したり削除したりします。RESTRICTは外部キーで参照されている場合に削除できなくなります。 INHERITSを指定すると作成済みのテーブルを継承して新しいテーブルを作成できます。データが保存される実表のことを基底表といいます。
-
(2) 変更(ALTER)
- ・書式：ALTER TABLE スキーマ名.テーブル名 [処理]
- ・説明：「ALTER TABLE」で作成したテーブルのカラム追加(ADD)・変更(ALTER)・削除(DROP)・制約変更をを行います。
-
(3) 削除(DROP)
- ・書式：DROP TABLE スキーマ名.テーブル名
- ・説明：「DROP TABLE」で作成したテーブルを削除します。削除するテーブルに外部キー制約がある場合、CASCADEを指定することで合わせてテーブルを削除できます。外部キー制約のみが削除されます。
-
(4) レコード全削除(TRUNCATE)
- ・書式：TRUNCATE TABLE スキーマ名.テーブル名
- ・説明：「TRUNCATE TABLE」でテーブル内のレコードを全て削除します。指定されたテーブル、または、CASCADEにより削除対象テーブルとされたテーブルを参照する外部キーを持つテーブルすべてを自動的に空にします。
本サイトで利用する各テーブルを作成します。
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.quiz_view_type(id smallserial PRIMARY KEY,quiz_view_type_name character varying(512) COLLATE pg_catalog."default" NOT NULL)'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.ipa_type(id smallserial PRIMARY KEY,ipa_type_name character varying(512) COLLATE pg_catalog."default" NOT NULL)'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.user(id bigserial PRIMARY KEY,nicname character varying(50) COLLATE pg_catalog."default" NOT NULL,password character varying(4096) COLLATE pg_catalog."default" NOT NULL,sex smallint NOT NULL,age smallint NOT NULL,create_time timestamp NOT NULL)'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.quiz_class(id smallserial PRIMARY KEY NOT NULL,quiz_class_name character varying(512) COLLATE pg_catalog."default" NOT NULL)'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.quiz_type(id smallserial PRIMARY KEY,quiz_type_name character varying(512) COLLATE pg_catalog."default" NOT NULL,quiz_class_id smallint NOT NULL references quiz_class(id))'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.quiz_ipa(parent_id smallint NOT NULL,ipa_type smallint NOT NULL references ipa_type(id),quiz_type smallint NOT NULL references quiz_type(id),ipa_year smallint NOT NULL,ipa_no smallint NOT NULL,PRIMARY KEY (quiz_type,ipa_year,ipa_no))'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.answer(answer_group_id bigint NOT NULL,answer character varying(4096) COLLATE pg_catalog."default" NOT NULL,consecutive_numbers smallint NOT NULL,PRIMARY KEY ( answer_group_id,consecutive_numbers))'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.quiz(id bigserial PRIMARY KEY NOT NULL,quiz character varying(4096) COLLATE pg_catalog."default" NOT NULL,quiz_image_url character varying(512) COLLATE pg_catalog."default",quiz_type smallint NOT NULL references quiz_type(id),quiz_view_type smallint NOT NULL references quiz_view_type(id),answer_group_id bigint NOT NULL,right_consecutive_numbers smallint NOT NULL,answer_explanation character varying(8192) COLLATE pg_catalog."default",reference_url character varying(512) COLLATE pg_catalog."default",FOREIGN KEY (answer_group_id, right_consecutive_numbers) REFERENCES answer (answer_group_id, consecutive_numbers))'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.quiz_history(id bigserial PRIMARY KEY,user_id bigint NOT NULL REFERENCES public.user(id),quiz_id bigint NOT NULL REFERENCES quiz(id),answer_group_id bigint NOT NULL,answer_id smallint NOT NULL,quiz_view_time bigint NOT NULL,quiz_answer_time bigint NOT NULL,quiz_right boolean NOT NULL,FOREIGN KEY (answer_group_id, answer_id) REFERENCES answer (answer_group_id, consecutive_numbers))'
!sudo -u postgres psql -U postgres -d quiz -c 'CREATE TABLE public.user_history(id bigserial PRIMARY KEY,user_id bigint references public.user(id),nicname character varying(50) COLLATE pg_catalog."default" NOT NULL,password character varying(4096) COLLATE pg_catalog."default" NOT NULL,sex smallint NOT NULL,age smallint NOT NULL,update_time timestamp NOT NULL)'
インデックス
-
(1) 作成(CREATE)
- ・書式：CREATE INDEX ON スキーマ名.テーブル名 (カラム名)
- ・説明：「CREATE INDEX」で指定したカラム(複数可)に対して新しいインデックスを作成します。
-
(2) 変更(ALTER)
- ・書式：ALTER INDEX インデックス名 [処理]
- ・説明：「ALTER INDEX」で作成したインデックスの変更を行います。
-
(3) 削除(DROP)
- ・書式：DROP INDEX インデックス名
- ・説明：「DROP INDEX」で作成したインデックスを削除します。
ロール
-
(1) 作成(CREATE)
- ・書式：CREATE ROLE ロール名 [オプション]
- ・説明：「CREATE ROLE」で新しいロールを作成します
-
(2) 変更(ALTER)
- ・書式：ALTER ROLE ロール名 [オプション]
- ・説明：「ALTER ROLE」でロールの変更を行います。
- ・例：ALTER ROLE wds RENAME TO wdsuser;
[オプション]
- RENAME TO [新しいロール名]
- [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
- [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
- [ IN DATABASE database_name ] RESET configuration_parameter
- [ IN DATABASE database_name ] RESET ALL
-
(3) 削除(DROP)
- ・書式：DROP ROLE ロール名
- ・説明：「DROP ROLE」で作成したロールを削除します。
ビュー
ビューは正規化されたテーブルを結合または必要な情報の導出を行い導出表としてユーザが簡単に問合せが行え、パスワード・個人情報などのユーザに不要なデータを隠蔽してセキュリティを確保するために使用されます。
一つの表に対して更新可能なビューが定義されている場合、元となる表に対してそのビューの所有者がもっているすべての権限が自動的に付与されます。
通常、ビューでは問合せのみですが、マテリアライズドビューとして作成することで更新可能なビューになりテーブルと同じようにINSERT、UPDATE、DELETEが行えるようになります。
ただ、更新可能なのはビュー作成時に、GROUP化されていない・関数・HAVING、LIMIT、OFFSETを使用していない必要があります。
-
(1) 作成(CREATE)
- ・書式：CREATE [MATERIALIZED] VIEW ビュー名,カラム名 AS SELECTクエリ
- ・説明：「CREATE VIEW」で新しいビューを作成します。
-
(2) 変更(ALTER)
- ・書式：ALTER VIEW ビュー名
- ・説明：「ALTER VIEW」で作成したビューを変更します。
-
(3) 削除(DROP)
- ・書式：DROP VIEW ビュー名
- ・説明：「DROP VIEW」で作成したビューを削除します。
ルール
指定したテーブルに対して指定したイベント(SELECT・INSERT・UPDATE・DROP等)が実行されたときに追加のコマンドを実行します。
-
(1) 作成(CREATE)
- ・書式：CREATE RULE ルール名 AS ON [イベント] TO テーブル名 DO [追加コマンド]
- ・説明：「CREATE RULE」で新しいルールを作成します。
-
(2) 変更(ALTER)
- ・書式：ALTER RULE ルール名 ON テーブル名
- ・説明：「ALTER RULE」で作成したルールを変更します。
-
(3) 削除(DROP)
- ・書式：DROP RULE ルール名 ON テーブル名
- ・説明：「DROP RULE」でルールを削除します。
トリガー
ルールと似ていますが、指定したテーブルに対して指定したイベントが発生したときに指定した関数を実行します。 値のチェックが必要な場合などは、こちらのトリガーを指定します。
-
(1) 作成(CREATE)
- ・書式：CREATE TRIGGER トリガー名 イベント ON テーブル名 EXECUTE PROCEDURE 関数名
- ・説明：「CREATE TRIGGER」で新しいトリガーを作成します。
-
(2) 変更(ALTER)
- ・書式：ALTER TRIGGER トリガー名 ON テーブル名
- ・説明：「ALTER TRIGGER」でトリガーを変更します。
-
(3) 削除(DROP)
- ・書式：DROP TRIGGER トリガー名 ON テーブル名
- ・説明：「DROP TRIGGER」でトリガーを削除します。
シーケンス
決められたルールで採番を行う番号を生成します。
nextval()関数で次の新しい番号に更新され取得できます。
-
(1) 作成(CREATE)
- ・書式：CREATE SEQUENCE シーケンス名 START 開始番号
- ・説明：「CREATE SEQUENCE」でシーケンスを作成します。
-
(2) 変更(ALTER)
- ・書式：ALTER SEQUENCE シーケンス名
- ・説明：「ALTER SEQUENCE」を変更します。
-
(3) 削除(DROP)
- ・書式：DROP SEQUENCE シーケンス名
- ・説明：「DROP 」はビューを削除します。
関数定義
一連の処理をまとめて行う関数を定義します。
関数は、「SELECT * FROM 関数名(引数名,引数名....)」で呼び出すことができます。
関数は、LANGUAGEを「SQL」「PL/pgSQL」「内部関数」「C言語関数」の4つがあります。
-
(1) 作成(CREATE)
- ・書式：CREATE FUNCTION 関数名
- ・説明：「CREATE FUNCTION」で関数を作成します。
-
(2) 変更(ALTER)
- ・書式：ALTER FUNCTION 関数名
- ・説明：「ALTER FUNCTION」で関数を変更します。
-
(3) 削除(DROP)
- ・書式：DROP FUNCTION 関数名
- ・説明：「DROP FUNCTION」は関数を削除します。
プロシージャ定義
プロシージャを定義します。
関数とほぼ同じですが、呼び出し方が違います。 「CALL プロシジャー名(引数,引数....)」
-
(1) 作成(CREATE)
- ・書式：CREATE PROCEDURE プロシージャ名
- ・説明：「CREATE PROCEDURE」でプロシージャを作成します。
-
(2) 変更(ALTER)
- ・書式：ALTER PROCEDURE プロシージャ名
- ・説明：「ALTER PROCEDURE」でプロシージャを変更します。
-
(3) 削除(DROP)
- ・書式：DROP PROCEDURE プロシージャ名
- ・説明：「DROP PROCEDURE」はビューを削除します。