【PostgreSQL】テーブルに対する列の追加、削除、列名変更方法のまとめ
PostgreSQLでのテーブルに対する列の追加、削除、列名の変更、制約の変更方法を毎回検索しているので記事にまとめる。
動作確認はPostgreSQL 11.6で行っている。
動作確認用テーブルの作成
SQLの動作確認のために以下のようにテーブルと初期データを作成する。
-- 部署テーブル
create table department (
id serial,
department_code text not null,
name text not null,
constraint pk_department primary key (id)
);
-- 社員テーブル
create table employee (
id serial,
name text not null,
age int,
branch_id int,
employee_code text,
department_id int,
constraint pk_employee primary key(id),
constraint uk_employee unique (branch_id, employee_code),
constraint fk_employee foreign key (department_id) references department (id)
);
-- データ投入
insert into department (department_code, name)
values
('dep1', 'sales'),
('dep2', 'marketing');
insert into employee (name, age, branch_id, department_id)
values
('employee1', 25, 1, 1),
('employee2', 26, 2, 2);列の追加
以下の構文のSQLで列を追加できる。
-- 列の追加
alter table {テーブル名} add column {列名} {型};
-- 例
alter table department add column description text;注意点としてはNOT NULL制約を持つ列を追加したい場合、列を追加後にUPDATE文により値を設定しないと追加した列の値がNULLであるためNOT NULL制約を追加することができない。
以下のように列追加、UPDATE文実行、NOT NULL制約追加を行う。
-- 列の追加
alter table department add column description text;
-- 値を設定
update department set description = '';
-- NOT NULL制約追加
alter table department alter column description set not null; 列名の変更
以下の構文のSQLで列名を変更できる。
-- 列名の変更
alter table {テーブル名} rename column {変更前の列名} to {変更後の列名};
-- 例
alter table department rename column description to department_description; 主キー、ユニークキー制約、外部キー制約に含まれる列も列名を変更可能である。
-- ユニーク制約に含まれる列名を変更
alter table employee rename column employee_code to employee_code2;
-- 外部キー制約に含まれる列名を変更
alter table department rename column id to id2;変更後のテーブル情報を確認してみると制約内の列名が変更されている。
変更前のemployeeテーブル情報
$ \d employee
Table "public.employee"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('employee_id_seq'::regclass)
name | text | | not null |
age | integer | | |
branch_id | integer | | |
employee_code | text | | |
department_id | integer | | |
Indexes:
"pk_employee" PRIMARY KEY, btree (id)
"uk_employee" UNIQUE CONSTRAINT, btree (branch_id, employee_code)
Foreign-key constraints:
"fk_employee" FOREIGN KEY (department_id) REFERENCES department(id)
列名の変更
alter table employee rename column employee_code to employee_code2;
alter table employee rename column id to id2;
alter table department rename column id to id2; 変更後のemployeeテーブル情報
$ \d employee
Table "public.employee"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+--------------------------------------
id2 | integer | | not null | nextval('employee_id_seq'::regclass)
name | text | | not null |
age | integer | | |
branch_id | integer | | |
employee_code2 | text | | |
department_id | integer | | |
Indexes:
"pk_employee" PRIMARY KEY, btree (id2)
"uk_employee" UNIQUE CONSTRAINT, btree (branch_id, employee_code2)
Foreign-key constraints:
"fk_employee" FOREIGN KEY (department_id) REFERENCES department(id2)列の削除
以下の構文のSQLで列を削除できる。
alter table {テーブル名} drop column {テーブル名};
-- 例
alter table department drop column description;ユニークキー制約に含まれる列を削除すると当該キーを含むユニークキー制約も削除される。例としてemployeeテーブルのbaranch_id列を削除する。
削除前のテーブル情報
$ \d employee
Table "public.employee"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('employee_id_seq'::regclass)
name | text | | not null |
age | integer | | |
branch_id | integer | | |
employee_code | text | | |
department_id | integer | | |
Indexes:
"pk_employee" PRIMARY KEY, btree (id)
"uk_employee" UNIQUE CONSTRAINT, btree (branch_id, employee_code)
Foreign-key constraints:
"fk_employee" FOREIGN KEY (department_id) REFERENCES department(id)branch_id列を削除をする。
alter table employee drop column branch_id;削除後のテーブル情報
“uk_employee" UNIQUE CONSTRAINT, btree (branch_id, employee_code)が無くなっている。
$ \d employee
Table "public.employee"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('employee_id_seq'::regclass)
name | text | | not null |
age | integer | | |
employee_code | text | | |
department_id | integer | | |
Indexes:
"pk_employee" PRIMARY KEY, btree (id)
Foreign-key constraints:
"fk_employee" FOREIGN KEY (department_id) REFERENCES department(id)テーブルに付与した制約の変更
制約を変更するには対象の制約を削除し、変更後の制約を追加する。例として、employeeテーブルのユニークキー制約をemployee_codeのみに変更する。
変更前のテーブル情報
$ \d employee
Table "public.employee"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('employee_id_seq'::regclass)
name | text | | not null |
age | integer | | |
branch_id | integer | | |
employee_code | text | | |
department_id | integer | | |
Indexes:
"pk_employee" PRIMARY KEY, btree (id)
"uk_employee" UNIQUE CONSTRAINT, btree (branch_id, employee_code)
Foreign-key constraints:
"fk_employee" FOREIGN KEY (department_id) REFERENCES department(id)-- ユニークキー制約を削除
alter table employee drop constraint uk_employee;
-- 新たなユニークキー制約を追加
alter table employee add constraint uk_employee unique (employee_code);変更後のテーブル情報
ユニークキー制約が変更できている。
$ \d employee
Table "public.employee"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('employee_id_seq'::regclass)
name | text | | not null |
age | integer | | |
branch_id | integer | | |
employee_code | text | | |
department_id | integer | | |
Indexes:
"pk_employee" PRIMARY KEY, btree (id)
"uk_employee" UNIQUE CONSTRAINT, btree (employee_code)
Foreign-key constraints:
"fk_employee" FOREIGN KEY (department_id) REFERENCES department(id)