はじめに

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 ビュー名

終わり

参考

[PostgreSQL]よく使うコマンドまとめ