PREPARATION
受験対策

OSS-DB入門

第3回 データベースとテーブルの作成

前回は実際にユーザを作成してユーザ管理とセキュリティ設定を行いました。今回はデータベースとテーブルを作成します。
データベースは、データを集めて管理し容易に検索・抽出などの再利用をできるようにしたものです。データを一元管理し、複数のユーザで最新のデータを共有することが可能です。ここでは実際にデータベースを作成してみることでデータベースについての理解を深めていきましょう。

1.データベースを作成

データベースを作成するには作成権限があるユーザで実行する必要があります。前回までと同様に端末を起動してください。前回作成したossdbユーザにデータベース作成権限を付与してみましょう。
まずはデータベースの管理者ユーザであるpostgresにsuし、psqlでデータベースに接続します。
ユーザにデータベース作成権限を付与するにはALTER USER文を利用します。

ALTER USER ossdb CREATEDB;

正しく権限が付与できたかどうか、「\du」メタコマンドで確認します。

\du

ossdbユーザの属性に「Create DB」が表示されていれば大丈夫です。
確認できたら\qでpsqlを終了し、さらにpostgresユーザのシェルからもexitして、ossdbユーザに戻ってください。

それでは「ossdb」という名前のデータベースを作成してみましょう。
データベースを作成するにはOSのコマンドプロンプトから、下記のように「createdb」コマンドを実行します。

$ createdb ossdb

データベースが作成されたか確認してみましょう。確認するためにデータベースの一覧を取得します。データベースの一覧はpsqlコマンドにオプションの「-l」を付けて実行することで取得できます。

$ psql -l

※データベースの一覧を取得する方法は他にもpsqlのメタコマンド「\l」や、PostgreSQLサーバに接続してシステムカタログを参照する方法(SELECT * FROM pg_database;)があります。psqlコマンドのオプションについては「$psql --help」でヘルプを参照することが可能です。
ossdbという名前のデータベースが一覧に表示されていたら無事に作成できています。なお、postgres, template0, template1は標準で作成されるデータベースです。

2.テーブルを作成

2.1 データ型

テーブルを作成する前に基本的なデータ型について触れておきます。

表1 商品管理表

商品名 1個あたりの金額 在庫数 入荷日 備考
りんご 150 10 2012/07/01 青森産
みかん 80 30 2012/07/02  

 上記のような商品管理表があったとします。データ(レコード)を参考にして項目名(カラム)毎にデータ型をみていきましょう。

2.1.1 文字列型

「商品名」や「備考」のように、文字が格納される項目は文字列型となります。文字列型にも種類があり、文字数の上限を指定したい場合はchar型(character型)とvarchar型(character varying型)を利用します。長さを無制限にしたい場合はtext型を利用します。

表2 文字列型

型名 説明
char(n), character(n) 文字数の上限を指定した固定長の文字列
varchar(n), character varying(n) 文字数の上限を指定した可変長の文字列
 text 可変長の文字列

 2.1.2 数値型

「在庫数」や「1個当たりの金額」のように、数値が格納される項目は数値型となります。整数値しか扱わない場合はinteger型、小数も扱う場合はnumeric型を利用します。それぞれのデータ型には範囲が指定されていて範囲外の数値を格納しようとするとエラーになります。

表3 数値型

 型名 サイズ 範囲
smallint 2バイト -32768~+32767
integer, int 4バイト -2147483648~+2147483647
bigint 8バイト -9223372036854775808~+9223372036854775807
decimal 可変長 最大1000桁、任意精度
numeric 可変長 最大1000桁、任意精度
real 4バイト 浮動小数点データ型、6桁の精度
double precision 8バイト 浮動小数点データ型、15桁の精度

2.1.3 日付/時刻型

「入荷日」のように、日付が格納される項目はdate型となります。date型以外にも時刻だけを格納するtime型や両方を同時に格納するtimestamp型があります。

この他にも連番を表現するserial型や、論理値型、幾何データ型など様々なデータ型が用意されています。

2.2 テーブル(表)の定義

テーブルはデータを格納する場所になります。二次元の表で管理され、各データの項目名は列として、個々のデータは行として並びます。
まずは先程作成したデータベースのossdbにpsqlコマンドで接続してください。

$ psql ossdb

先程の商品管理表に連番のIDを付与して、簡単な商品管理テーブルを作成してみましょう。テーブルを作成するにはCREATE TABLE文を利用します。テーブルの各列にはデータ型やデフォルト値を設定することが可能です。

CREATE TABLE products(
 id serial,
 name varchar(255),
 price integer,
 stock integer,
 arrival_date date,
 etc text
);

※カラム名は英数字のみを使うことを推奨します。また、大文字と小文字は区別されません。

作成されているテーブルの一覧はpsqlメタコマンドの「\d」、作成したテーブルの定義は「\d テーブル名」で確認できます。

\d products

 テーブルの定義が表示されたと思います。
ID列に利用したserial型では、テーブル作成時にinteger型シーケンスが自動的に生成されます。シーケンスは自動的に連番を生成するオブジェクトです。「Modifiers(修飾語)」の欄の「default...」の記述をみると、生成されたシーケンスを利用して自動的に連番をデフォルト値として格納するようになっています。

2.3 制約

制約はテーブルに格納するデータを限定するための方法です。上図の「Modifiers(修飾語)」の欄に「not null」と記述されています。これはNULLの格納を禁止するという制約となります。このように制約はテーブルの列に対して特定の条件を満たす値だけを許可したい場合に使用します。代表的な制約として、主キー制約、ユニーク制約や外部キー制約などがあります。制約はテーブルの作成時やテーブル定義の変更時に設定することが可能ですが、既にデータが登録してあるテーブルの制約を変更してしまうとデータに不整合が生じる恐れがあります。

実際に制約をつけて商品管理テーブルを作成してみます。例えば「商品名」は入力必須な項目、「在庫数」は0以上の数、「1個当たりの金額」は0より大きい数としたい場合、CREATE TABLE文は下記のようになります。

CREATE TABLE products(
 id serial,
 name varchar(255) NOT NULL,
 price integer CHECK (price > 0),
 stock integer CHECK (stock >= 0),
 arrival_date date,
 etc text
);

制約には名前を付けることが可能です。「1個当たりの金額」の制約に名前を付ける場合は以下の通りになり、priceの値は0より大きい値であることが制約により保証されるようになります。

price integer CONSTRAINT positive_price CHECK (price > 0)

あとがき

 今回はデータベースの基本からテーブルの作成方法まで学習しました。データ型や制約を工夫することでデータの管理が容易になります。例えば空間情報を利用したい場合にはPostGISを導入することでジオメトリ型、幾何関数、空間インデックス、座標系管理機能が拡張されます。データベース、データ型、テーブルの詳細についてはオンラインマニュアルもご参照ください。

・第5章データ定義
http://www.postgresql.jp/document/9.1/html/ddl.html

・第8章データ型
http://www.postgresql.jp/document/9.1/html/datatype.html

・第21章データベース管理
http://www.postgresql.jp/document/9.1/html/managing-databases.html

執筆:
市脇 康二 氏(OSS-DB Gold認定者)株式会社オークニー ジオソリューション事業部 マネージャー
PostGISを含めたFOSS4G(Free Open Source Software for Geospatial)ソフトウェアを活用し、地図やGPS等の位置情報を組み合わせたアプリケーションやサービスの設計・開発に従事。
サービスプラットフォームがクラウドへ、利用シーンがモバイルへと広がりを見せる中、位置情報の更なる活用シーンを模索中。

アドバイザー:
森 亮 氏 株式会社オークニー代表取締役
2002年にオークニーを設立し、代表取締役に就任。マッピング・ GIS・LBS・ITS関連プロジェクトのビジネスコンサルティングを経て、現在は会社経営の傍ら、『入門Webマッピング』(Tyler Mitchell原著;オライリー・ジャパン発行)の翻訳、Open Source Geospatial財団日本支部の代表者を務めるなど、FOSS4G(オープンソース地理空間ソフトウェア)の普及活動に取り組んでいる。

監修:
松田 神一

LPI-Japan
Platinum Sponsors

関連資格