テーブルの作成・列の変更のまとめ【PostgreSQL】

2021年8月22日未分類

【頻出】テーブルの作成・列の変更操作のまとめ

テーブルの作成・列の変更操作のうち、特に頻出のものをまとめました。

 

1.CREATE TABLE(+DELETE CASCADE)

/*従業員テーブルを作成し、
部署マスタ(department)の部署コードに対し外部キー制約を付ける(DELETE CASCADE)*/
CREATE TABLE employee
(
    employee_code character varying(10) NOT NULL,
	nm character varying(100),
	department_code  character varying(10),
	--(略)
    CONSTRAINT 主キー名 PRIMARY KEY (employee_code),  --主キー
	CONSTRAINT 外部キー名 FOREIGN KEY (department_code)    --外部キー
	REFERENCES department (department_code) ON DELETE CASCADE on update no action --DELETE CASCADE
);

--DELETE CASCADEしない時は、最後の行を「CASCADE」→「no action」にする
--例↓
--REFERENCES busyo (busyo_code) ON DELETE no action on update no action

ここで詳しく説明しています

テーブルを作成する(CREATE TABLE文の構文説明)

 

関連投稿

テーブルを作成する方法(pgAdminで作成する)

テーブルを作成する方法(CREATE文で作成する)

 

 

2.テーブル作成(テンプレ文)

--table1
CREATE TABLE table1
(
	cd1 integer not null,
	nm1 text,
	CONSTRAINT pk_table1 PRIMARY KEY (cd1)
);

--table2
CREATE TABLE table2
(
	cd2 integer not null,
	cd1 integer,
	nm2 text,
	CONSTRAINT pk_table2 PRIMARY KEY (cd2),
	CONSTRAINT fk_tabel2_table1 FOREIGN KEY (cd1)
	REFERENCES table1 (cd1) 
	ON UPDATE NO ACTION
	ON DELETE CASCADE
);

--テストデータ用(使用する場合はコメント解除)
/*
insert into table1 values(1,'A');
insert into table1 values(2,'B');
insert into table1 values(3,'C');
insert into table2 values(1,1,'a');
insert into table2 values(2,1,'b');
insert into table2 values(3,2,'c');
insert into table2 values(4,2,'d');
insert into table2 values(5,3,'e');
insert into table2 values(6,3,'f');
*/

 

 

3.列の追加・変更・削除

--列の追加
ALTER TABLE テーブル名 add 列名 型;

--Not Null制約の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL;

--デフォルト値の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT デフォルト値;

--列の削除
ALTER TABLE テーブル名 DROP COLUMN 列名;

--外部キー制約の追加
ALTER TABLE テーブル名
ADD FOREIGN KEY (外部キーを付けるテーブルの列名) REFERENCES 参照先テーブル名 (参照先列名);

--インデックスの作成
CREATE INDEX インデックス名 ON テーブル名 (列名1 , 列名2 , ・・・ );

--インデックスの削除
DROP INDEX インデックス名;

ここで詳しく説明しています

テーブルに列を追加する、削除する方法、桁数を変更する方法

列にデフォルト値(初期値)を追加・削除する方法

列にNOT NULL制約を追加・削除する方法

外部キーを追加・削除する方法

プライマリーキーとユニークキーの違い

INDEXを追加、削除する

INDEXを再構築する(REINDEX)

 

 

4.データベース・テーブル・列にコメントを付加する

COMMENT ON DATABASE データベース名 IS 'コメントです';    --データベースへコメント
COMMENT ON TABLE テーブル名 IS 'コメントです';           --テーブルへコメント
COMMENT ON COLUMN テーブル名.列名 IS 'コメントです';      --列へコメント
COMMENT ON COLUMN テーブル名.列名 IS NULL;               --コメントの削除

ここで詳しく説明しています

データベース、テーブル、列へコメントをつける

 

 

5.生成列(自動で計算される列)

--生成列を作る(CREATE TABLEの列に書く)
列名 型 GENERATED ALWAYS AS 式 STORED

CREATE TABLE public.test
(   ・・・
    height numeric,
    weight numeric,
    --生成列 : BMI( 体重 ÷ 身長(m)の2乗)の作成
    bmi numeric(3,1) GENERATED ALWAYS AS (weight / ((height / 100) * (height / 100))) STORED,
    ・・・
)

ここで詳しく説明しています

生成列を定義する

 

 

6.こちらもおすすめ

文字型の型について整理してみた

数値型の型について整理してみた

booleanについて整理してみた

textとvarcharの違い

timestampのwithout time zoneとwith time zoneの違い

textからvarcharへの変換、varcharからtextへの変換の方法

シーケンス(serial)を作成する

列挙型(ENUM)の書き方