目次
はじめに
本記事では、任意の列にデフォルト値を設定する方法をご案内いたします。デフォルト値を設定することで、COPYやINSERTの実行時、設定したデフォルト値が自動的に挿入されます。
構文
デフォルト値は、CREATE文およびALTER文の列定義にDEFAULTオプションを指定することで設定できます。CREATE文
|
1 |
CREATE TABLE テーブル名(...列名 DEFAULT 任意のデフォルト値) |
ALTER文
|
1 2 3 4 5 |
1．既存の列定義を変更（※列定義の変更時、デフォルト値は挿入されません。） ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT 任意のデフォルト値; 2．新規に列を追加（※列追加時、デフォルト値が挿入されます。） ALTER TABLE テーブル名 ADD 列名 DEFAULT 任意のデフォルト値; |
※デフォルト値には本記事で例示するもの以外にも様々な値を設定できます。
詳しくは後述の「サポートされるデフォルト設定値」をご確認ください。
実行例
本例では、col2_length列にデフォルト値を設定したtestテーブルを作成します。col2_length列では、LENGTH関数を使用し、col2列の値の文字数が数値データとして挿入されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--テーブル作成と定義の確認 dbadmin=> CREATE TABLE test(col1 INT, col2 VARCHAR, col2_length INT DEFAULT LENGTH(col2)); CREATE TABLE dbadmin=> \d test List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+-------+-------------+-------------+------+-------------------+----------+-------------+------------- public | test | col1 | int | 8 | | f | f | public | test | col2 | varchar(80) | 80 | | f | f | public | test | col2_length | int | 8 | length(test.col2) | f | f | (3 rows) |
COPY文
COPYコマンドではDEFAULT値を設定したcol2_length列以外の、col1,col2を指定します。CSVに記述されたデータが、指定したcol1列,col2列にロードされ、col2_length列には設定したデフォルト値が挿入されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
--ファイルの内容 dbadmin=> \! cat /tmp/csv/test.csv 1,aa 2,bbb 3,cccc --COPYによるデータロード dbadmin=> COPY test(col1,col2) FROM '/tmp/csv/test.csv' DELIMITER ',' ABORT ON ERROR; Rows Loaded ------------- 3 (1 row) --ロード結果の確認 dbadmin=> SELECT * FROM test ORDER BY col1; col1 | col2 | col2_length ------+------+------------- 1 | aa | 2 ←col2列の文字数が挿入されています 2 | bbb | 3 3 | cccc | 4 (3 rows) |
INSERT文
INSERT文では、VALUESに指定したデータが、列指定したcol1列,col2列にロードされ、col2_length列には設定したデフォルト値が挿入されます。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--INSERTによるデータロード dbadmin=> INSERT INTO test(col1, col2) VALUES(4,'ddddd'); OUTPUT -------- 1 (1 row) --ロード結果の確認 dbadmin=> SELECT * FROM test ORDER BY col1; col1 | col2 | col2_length ------+-------+------------- 1 | aa | 2 2 | bbb | 3 3 | cccc | 4 4 | ddddd | 5 (4 rows) |
既存データに更新があった際の注意点
一度挿入されたデフォルト値は自動更新されません。そのため、既存データを更新する際は、手動で更新処理を行う必要があります。
以下のコマンドを使用し、デフォルト値設定列のデータを更新します。
|
1 |
UPDATE 表名 SET 列名=DEFAULT; |
既存データを更新する際の対応例を以下に記載します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
--現在のデータの確認 dbadmin=> SELECT * FROM test ORDER BY col1; col1 | col2 | col2_length ------+------+------------- 1 | aa | 2 ←col2列の文字数が数値データで挿入されています 2 | bbb | 3 3 | cccc | 4 4 | ddddd | 5 (4 rows) --デフォルト値として参照されているcol2列の値を更新 dbadmin=> UPDATE test SET col2='aaaaaa' WHERE col1=1; OUTPUT -------- 1 (1 row) --デフォルト値が自動的に更新されないことを確認 dbadmin=> SELECT * FROM test ORDER BY col1; col1 | col2 | col2_length ------+--------+------------- 1 | aaaaaa | 2 ←更新後のcol2列の文字数が反映されていません 2 | bbb | 3 3 | cccc | 4 4 | ddddd | 5 (4 rows) --デフォルト値設定列の更新処理 dbadmin=> UPDATE test SET col2_length=DEFAULT WHERE col1=1; OUTPUT -------- 1 (1 row) --更新後のデータ確認 dbadmin=> SELECT * FROM test ORDER BY col1; col1 | col2 | col2_length ------+--------+------------- 1 | aaaaaa | 6 ←最新のcol2列の文字数に更新されました 2 | bbb | 3 3 | cccc | 4 4 | ddddd | 5 (4 rows) |
サポートされるデフォルト設定値
サポートされるデフォルト値は以下です。・クエリ
・同じテーブルの他の列
・リテラル
・Verticaがサポートする全ての演算子
・以下の関数
※参考として、それぞれの関数のマニュアルのURLを追記しています
NULL処理関数
https://docs.vertica.com/12.0.x/en/sql-reference/functions/null-handling-functions/
ユーザ定義の関数
https://docs.vertica.com/12.0.x/en/extending/developing-udxs/scalar-functions-udsfs/
システム情報関数
https://docs.vertica.com/12.0.x/en/sql-reference/functions/system-information-functions/
文字列関数
https://docs.vertica.com/12.0.x/en/sql-reference/functions/data-type-specific-functions/string-functions/
数学的な関数
https://docs.vertica.com/12.0.x/en/sql-reference/functions/mathematical-functions/
書式設定関数
https://docs.vertica.com/12.0.x/en/sql-reference/functions/formatting-functions/
参考情報
Defining column valueshttps://docs.vertica.com/12.0.x/en/admin/working-with-native-tables/managing-table-columns/defining-column-values/
検証バージョンについて
この記事の内容はVertica 12.0で確認しています。更新履歴
2023/06/21 Vertica12.0用に検証バージョンを修正2018/09/03 本記事を公開
