はじめに
postgresqlでDB構築をする記事。
導入
amazon-linuxにて導入します。バージョンは特にこだわらず。。
$ sudo yum update -y
$ sudo yum install -y libxslt.i686
$ sudo yum install -y postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs
$ sudo /sbin/service postgresql initdb
$ sudo /sbin/service postgresql start
$ sudo su - postgres
sudo yum install postgresqlだけでは足りないんですね。
DBの起動
$ psql -d データベース名;
postgresをまず起動します。
$ psql -d postgres;
※パスワードでエラーが出る場合は
$ psql -d postgres -U postgres
で管理者を指定して、パスワードpostgresで入ってください。
※#SHOW data_directory;でデータベースクラスタの場所を確認できる
DBの作成と削除
DBの作成
# CREATE DATABASE データベース名;
kenkoという名のDBを作成します。
# CREATE DATABASE kenko;
DB一覧の確認
# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
kenko | hoge | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | hoge | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
権限の指定もできます。こちらを参照に。
削除する時は以下を入力します。
# DROP DATABASE データベース名;
テーブルの作成
テーブルは以下のように作成をすることができます。
# CREATE TABLE テーブル名
# (カラム1 データ型 制約,
# カラム1 データ型 制約,
# カラム1 データ型 制約,
# PRIMARY KEY(カラム1));
列ごとに以下のような制約をつけることができます。
| 制約 | 意味 |
|---|---|
| PRIMARY KEY | 主キーとして利用。 |
| UNIQUE | 同列内で重複する値を避ける。 |
| REFERENCES 表名(列名) | 外部キーとする。参照先は指定した表名(列名) |
| CHECK(条件) | 条件に合った値のみの入力 |
| NOT NULL | NULLは入力できない。 |
| DEFAULT 値 | デフォルト値の指定 |
こちらを参考。
先ほど作ったkenkoというDBにkenko_recordを作成します。
# CREATE TABLE kenko_record
# (id char(4) not null,
# name text not null,
# food text not null,
# PRIMARY KEY(id));
以下のコマンドでビューを含むテーブル一覧
# \d
テーブルができてます。
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+--------
public | kenko_record | table | hiropy
(1 row)
以下のコマンドでビューを含まないテーブル一覧
# \dt
補足：データ型
文字型
| データ型 | 最大長 | 概要 |
|---|---|---|
| VARCHAR（n） | 1GB | 長さn文字の可変長文字列 |
| CHAR（n） | 1GB | 長さn文字の固定長文字データ。指定した長さより短い値を挿入したときは、残りは空白で埋められる |
| TEXT | 1GB | 長さ指定なしの可変長文字列 |
数値型
| データ型 | 最大長 | 概要 |
|---|---|---|
| INTEGER | 4バイト | 整数型。数値の範囲と保存のサイズ、性能のバランスNUMBERが良い |
| SMALLINT | 2バイト | 範囲の狭い整数型 |
| BIGINT | 8バイト | 範囲の広い整数型 |
| NUMERIC | 1000桁 | 正と負の固定小数点数。小数点より右側の桁数と全体の桁数を指定できる |
| REAL | 4バイト | 単精度の浮動小数点数 |
| DOUBLE PRECISION | 8バイト | 倍精度の浮動小数点数 |
日付型
| データ型 | 最大長 | 概要 |
|---|---|---|
| DATE | 4バイト | 1日単位で日付のみを表すデータ |
| TIMESTAMP | 8バイト | 日付と時刻の両方を表すデータ |
バイナリ型
| データ型 | 最大長 | 概要 |
|---|---|---|
| bytea | 1GB | 可変長のバイナリデータ |
| ラージオブジェクト | 2GB | データベース内に格納される |
DBへの登録
insert文で、カラムに対応したデータ型でデータを登録することができます。
# INSERT INTO テーブル名
# (カラム１,カラム２,カラム３) VALUES
# (カラム１の新データ, カラム2の新データ, カラム3の新データ);
複数のレコードを登録する時は以下のように、連続して記述することで、登録できます。
# INSERT INTO テーブル名
# (カラム１,カラム２,カラム３)VALUES
# (カラム１の新データ, カラム2の新データ, カラム3の新データ),
# (カラム１の新データ, カラム2の新データ, カラム3の新データ),
# (カラム１の新データ, カラム2の新データ, カラム3の新データ);
こちらを参考。
先ほど作ったkenko_recordテーブルに、データを登録します。
kenko=# INSERT INTO kenko_record(id,name,food) VALUES (1,'hoge','おにぎり');
一応select文で内容を確認しておきます。
kenko=# select * from kenko_record;
以下のように返ってきました。
id | name | food
------+------+----------
1 | hoge | おにぎり
(1 row)
ついでにcsvファイルにメモしていたものを追加
読み込む前に、カラムが一致しているかファイル確認。※catでもlessでもワイルドカードはなんでもいいです。
$ more health.csv
<U+FEFF>id,name,food^M2,hoge2,サンドイッチ^M3,hoge3,焼肉^M4,hoge4,うどん
一応テーブル定義も見ておきます。
# \d kenko_record;
\COPYコマンドを使ってCSVをテーブルに投入します。
COPY 3 と返ってきたので大丈夫そうですね。
# \copy kenko_record from 'health.csv' csv header;
COPY 3
一応確認
# select * from kenko_record;
id | name | food
------+-------+--------------
1 | hoge | おにぎり
2 | hoge2 | サンドイッチ
3 | hoge3 | 焼肉
4 | hoge4 | うどん
CSV取り込みに関してはこちら が分かりやすいです。
テーブルの変更と削除
変更は少し長いです。
# ALTER TABLE テーブル名 rename column 変更したいカラム名 to 新しいカラム名;
削除は簡単です。これだけ。
# DROP TABLE テーブル名;
パラメタに関してはこちらを参考。
ビューの作成と削除
今回は、まだビューを作る必要はないですが、複雑なクエリをビューにしておくことで、参照がしやすくなったり、見せたくないカラムなどを隠すことができます。
# CREATE VIEW ビュー名 AS SELECT カラム名 FROM テーブル名;
削除も簡単。
# DROP VIEW ビュー名