PostgreSQL での列のデータ型変更について
PostgreSQL のテーブルである列のデータ型を変更したい場合、互換性のある列データ型への変更は次のように ALTER TABLE ALTER COLUMN で変更できます。ALTER TABLE [テーブル名] ALTER COLUMN [列名] TYPE [新しいデータ型];
しかし実際にはデータ型の変更は、データ型変更前後のデータ型間で互換性のある変更なのか、互換性のないデータ型変更なのかによって変更方法が若干異なります。
列データ型を変更するテストテーブル作成
では実際に列データ型の互換性がある場合の列データ型を変更してみます。 ここでは int 型(integer)から bigint 型に変更してみますが、これらは同じ数値データ型で 互換性があります。CREATE TABLE population (
rank int,
country text NOT NULL,
region text,
population text
);
=> \d population
Table "public.population"
Column | Type | Modifiers
------------+---------+-----------
rank | integer |
country | text | not null
region | text |
population | bigint |
またテストデータも登録しておきましょう。
テスト用に "population.csv" という CSV ファイルを用意しました。これは 2017/07/01 時点での正解の人口ランキングの上位３０カ国を示すデータです。 この CSV データを COPY コマンドを使ってテーブルに登録します。日本ってこんなに上位にいるんですね！？
[population.csv]
rank,country,region,population
1,China,Asia,"1,409,517,397"
2,India,Asia,"1,339,180,127"
3,United States,Americas,"324,459,463"
4,Indonesia,Asia,"263,991,379"
5,Brazil,Americas,"209,288,278"
6,Pakistan,Asia,"197,015,955"
7,Nigeria,Africa,"190,886,311"
8,Bangladesh,Asia,"164,669,751"
9,Russia,Europe,"143,989,754"
10,Mexico,Americas,"129,163,276"
11,Japan,Asia,"127,484,450"
12,Ethiopia,Africa,"104,957,438"
13,Philippines,Asia,"104,918,090"
14,Egypt,Africa,"97,553,151"
15,Vietnam,Asia,"95,540,800"
16,Germany,Europe,"82,114,224"
17,Congo,Africa,"81,339,988"
18,Iran,Asia,"81,162,788"
19,Turkey,Asia,"80,745,020"
20,Thailand,Asia,"69,037,513"
21,United Kingdom,Europe,"66,181,585"
22,France,Europe,"64,979,548"
23,Italy,Europe,"59,359,900"
24,Tanzania,Africa,"57,310,019"
25,South Africa,Africa,"56,717,156"
26,Myanmar,Asia,"53,370,609"
27,South Korea,Asia,"50,982,212"
28,Kenya,Africa,"49,699,862"
29,Colombia,Americas,"49,065,615"
30,Spain,Europe,"46,354,321"
=> \copy population from 'population.csv' csv header
=> select * from population ;
------+----------------+----------+---------------
rank | country | region | population
------+----------------+----------+---------------
1 | China | Asia | 1,409,517,397
2 | India | Asia | 1,339,180,127
3 | United States | Americas | 324,459,463
4 | Indonesia | Asia | 263,991,379
5 | Brazil | Americas | 209,288,278
6 | Pakistan | Asia | 197,015,955
7 | Nigeria | Africa | 190,886,311
8 | Bangladesh | Asia | 164,669,751
9 | Russia | Europe | 143,989,754
10 | Mexico | Americas | 129,163,276
11 | Japan | Asia | 127,484,450
12 | Ethiopia | Africa | 104,957,438
13 | Philippines | Asia | 104,918,090
14 | Egypt | Africa | 97,553,151
15 | Vietnam | Asia | 95,540,800
16 | Germany | Europe | 82,114,224
17 | Congo | Africa | 81,339,988
18 | Iran | Asia | 81,162,788
19 | Turkey | Asia | 80,745,020
20 | Thailand | Asia | 69,037,513
21 | United Kingdom | Europe | 66,181,585
22 | France | Europe | 64,979,548
23 | Italy | Europe | 59,359,900
24 | Tanzania | Africa | 57,310,019
25 | South Africa | Africa | 56,717,156
26 | Myanmar | Asia | 53,370,609
27 | South Korea | Asia | 50,982,212
28 | Kenya | Africa | 49,699,862
29 | Colombia | Americas | 49,065,615
30 | Spain | Europe | 46,354,321
------+----------------+----------+---------------
(30 rows)
データ型を互換性のあるデータ型に変更
それでは早速テーブルの列のデータ型を互換性のあるデータ型に変更してみます。意図した通りにデータ型が int 型(integer)から bigint 型に変更されており、 もちろんデータは何も変更されずにそのまま残っています。
=> ALTER TABLE population ALTER COLUMN rank TYPE bigint;
ALTER TABLE
=> \d population
Table "public.population"
Column | Type | Modifiers
------------+--------+-----------
rank | bigint |
country | text | not null
region | text |
population | bigint |
データ型を互換性のないデータ型に変更
互換性のあるデータ型変更は問題なく変更できましたが、今度は先のテーブルの "population" 列の値にカンマ(,)が入っています。このカンマがあると集計処理がやりにくいのでカンマを全部削除し、text 型になっている列データ型を数値型 (bigint) に変更します。まず replace 関数を使ってカンマを全部削除します。
=> UPDATE population SET population = replace(population, ',', '') ;
UPDATE 30
=> SELECT * FROM population ;
rank | country | region | population
------+----------------+----------+------------
1 | China | Asia | 1409517397
2 | India | Asia | 1339180127
3 | United States | Americas | 324459463
4 | Indonesia | Asia | 263991379
5 | Brazil | Americas | 209288278
6 | Pakistan | Asia | 197015955
7 | Nigeria | Africa | 190886311
8 | Bangladesh | Asia | 164669751
9 | Russia | Europe | 143989754
10 | Mexico | Americas | 129163276
11 | Japan | Asia | 127484450
12 | Ethiopia | Africa | 104957438
13 | Philippines | Asia | 104918090
14 | Egypt | Africa | 97553151
15 | Vietnam | Asia | 95540800
16 | Germany | Europe | 82114224
17 | Congo | Africa | 81339988
18 | Iran | Asia | 81162788
19 | Turkey | Asia | 80745020
20 | Thailand | Asia | 69037513
21 | United Kingdom | Europe | 66181585
22 | France | Europe | 64979548
23 | Italy | Europe | 59359900
24 | Tanzania | Africa | 57310019
25 | South Africa | Africa | 56717156
26 | Myanmar | Asia | 53370609
27 | South Korea | Asia | 50982212
28 | Kenya | Africa | 49699862
29 | Colombia | Americas | 49065615
30 | Spain | Europe | 46354321
(30 rows)
では互換性のないデータ型変更 (text から bigint) に変更してみましょう。
=> ALTER TABLE population ALTER COLUMN population TYPE bigint; ERROR: column "population" cannot be cast to type bigint"population" 列のデータ型を bigint にキャスト(型変換)出来ないというエラーになりました。
互換性のないデータ型への変更を行うには、"USING" という命令の後に明示的にデータ型を変更 する指示を行って上げる必要があります。text 型の値を bigint に変換するには「population::bigint」と することで変換可能です。
ということで、次のようにするとデータ型が変更できます。そしてもちろんデータは何も変更されていません。
=>
ALTER TABLE population
ALTER COLUMN population
TYPE bigint USING population::bigint;
=> \d population
Table "public.population"
Column | Type | Modifiers
------------+---------+-----------
rank | integer |
country | text | not null
region | text |
population | bigint |
=> SELECT * FROM population ;
rank | country | region | population
------+----------------+----------+------------
1 | China | Asia | 1409517397
2 | India | Asia | 1339180127
3 | United States | Americas | 324459463
4 | Indonesia | Asia | 263991379
5 | Brazil | Americas | 209288278
6 | Pakistan | Asia | 197015955
7 | Nigeria | Africa | 190886311
8 | Bangladesh | Asia | 164669751
9 | Russia | Europe | 143989754
10 | Mexico | Americas | 129163276
11 | Japan | Asia | 127484450
12 | Ethiopia | Africa | 104957438
13 | Philippines | Asia | 104918090
14 | Egypt | Africa | 97553151
15 | Vietnam | Asia | 95540800
16 | Germany | Europe | 82114224
17 | Congo | Africa | 81339988
18 | Iran | Asia | 81162788
19 | Turkey | Asia | 80745020
20 | Thailand | Asia | 69037513
21 | United Kingdom | Europe | 66181585
22 | France | Europe | 64979548
23 | Italy | Europe | 59359900
24 | Tanzania | Africa | 57310019
25 | South Africa | Africa | 56717156
26 | Myanmar | Asia | 53370609
27 | South Korea | Asia | 50982212
28 | Kenya | Africa | 49699862
29 | Colombia | Americas | 49065615
30 | Spain | Europe | 46354321
(30 rows)
このようにデータ型変更前後で、データ系に互換性があるかないかによって変更方法が若干異なるということです。
