誰でも分かる！PostgreSQLでDB構築！

# はじめに
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の起動

```terminal
$ psql -d データベース名;
```
postgresをまず起動します。

```terminal
$ psql -d postgres;
```

※パスワードでエラーが出る場合は

```
$ psql -d postgres -U postgres
```
で管理者を指定して、パスワードpostgresで入ってください。

※`#SHOW data_directory;`でデータベースクラスタの場所を確認できる

# DBの作成と削除

DBの作成

```sql
# CREATE DATABASE データベース名;
```
kenkoという名のDBを作成します。

```sql
# CREATE DATABASE kenko;
```

DB一覧の確認

```sql
# \l
```

```terminal
                               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 | 

```


権限の指定もできます。[こちら](https://www.dbonline.jp/postgresql/database/index2.html)を参照に。

削除する時は以下を入力します。

```sql
# DROP DATABASE データベース名;
```

# テーブルの作成

テーブルは以下のように作成をすることができます。

```sql
# CREATE TABLE テーブル名
# (カラム1 データ型 制約,
# カラム1 データ型 制約,
# カラム1 データ型　制約,
# PRIMARY KEY(カラム1));
```

列ごとに以下のような制約をつけることができます。

| 制約 | 意味 |
|:--|:--|
| PRIMARY KEY | 主キーとして利用。 |
| UNIQUE | 同列内で重複する値を避ける。 |
| REFERENCES 表名(列名) | 外部キーとする。参照先は指定した表名(列名) |
| CHECK(条件) | 条件に合った値のみの入力 |
| NOT NULL | NULLは入力できない。 |
| DEFAULT 値 | デフォルト値の指定 |

[こちら](http://db-study.com/archives/233)を参考。

先ほど作ったkenkoというDBにkenko_recordを作成します。

```sql
# CREATE TABLE kenko_record
# (id char(4) not null,
# name text not null,
# food text not null,
# PRIMARY KEY(id));
```

以下のコマンドでビューを含むテーブル一覧

```sql
# \d
```
テーブルができてます。

```
           List of relations
 Schema |     Name     | Type  | Owner  
--------+--------------+-------+--------
 public | kenko_record | table | hiropy
(1 row)
```

以下のコマンドでビューを含まないテーブル一覧

```sql
# \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文で、カラムに対応したデータ型でデータを登録することができます。

```sql
# INSERT INTO テーブル名
# (カラム１,カラム２,カラム３) VALUES 
# (カラム１の新データ, カラム2の新データ, カラム3の新データ);
```
複数のレコードを登録する時は以下のように、連続して記述することで、登録できます。

```sql
# INSERT INTO テーブル名
# (カラム１,カラム２,カラム３)VALUES
# (カラム１の新データ, カラム2の新データ, カラム3の新データ),
# (カラム１の新データ, カラム2の新データ, カラム3の新データ),
# (カラム１の新データ, カラム2の新データ, カラム3の新データ);
```

[こちら](https://gray-code.com/php/insert-data-by-using-pdo/)を参考。

先ほど作ったkenko_recordテーブルに、データを登録します。

```sql
kenko=# INSERT INTO kenko_record(id,name,food) VALUES (1,'hoge','おにぎり');
```
一応select文で内容を確認しておきます。

```sql
kenko=# select * from kenko_record;
```
以下のように返ってきました。

```
  id  | name |   food   
------+------+----------
 1    | hoge | おにぎり
(1 row)
```

ついでにcsvファイルにメモしていたものを追加

読み込む前に、カラムが一致しているかファイル確認。※catでもlessでもワイルドカードはなんでもいいです。

```terminal
$ more health.csv
<U+FEFF>id,name,food^M2,hoge2,サンドイッチ^M3,hoge3,焼肉^M4,hoge4,うどん
```
一応テーブル定義も見ておきます。

```sql
# \d kenko_record;
```


\COPYコマンドを使ってCSVをテーブルに投入します。
COPY 3 と返ってきたので大丈夫そうですね。

```sql
# \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取り込みに関しては[こちら](http://everything-you-do-is-practice.blogspot.com/2017/10/postgresql-copy.html) が分かりやすいです。

# テーブルの変更と削除

変更は少し長いです。

```sql
# ALTER TABLE テーブル名 rename column 変更したいカラム名 to 新しいカラム名;
```

削除は簡単です。これだけ。

```sql
# DROP TABLE テーブル名;
```



パラメタに関しては[こちら](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_DROP_TABLE.html)を参考。

# ビューの作成と削除

今回は、まだビューを作る必要はないですが、複雑なクエリをビューにしておくことで、参照がしやすくなったり、見せたくないカラムなどを隠すことができます。

```sql
# CREATE VIEW ビュー名 AS SELECT カラム名 FROM テーブル名;
```
削除も簡単。

```sql
# DROP VIEW ビュー名
```

# 終わり

# 参考

[[PostgreSQL]よく使うコマンドまとめ](
https://dev.classmethod.jp/server-side/db/postgresql-organize-command/)