【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)

PostgreSQL, SQL

Posted by fanfanta