PostgreSQLの環境をmacのローカルに作成＆SQL操作（＆ついでにDataGripへの接続）

# 主に使うコマンドまとめ

一番最初に、今後よく使うコマンドをまとめておく

### サーバーの起動

```bash
% postgres -D /usr/local/var/postgres
```

このコマンドでpostgresサーバを起動し、起動させたら別のコンソールウィンドウを開いてpsqlコマンドを操作する。

### データベース一覧を見る

```sql
% psql -l
```

### データベースに接続

```bash
% psql -U ユーザー名　データベース名
```

### その他、sqlコマンド系

| sqlコマンド | 説明 | 
|:-----------:|:------------:|
| \l   | DB一覧表示 | 
| \c DB名 | 接続先DB変更 |
| \d     |  テーブル一覧|
| \d テーブル名 | テーブルの項目(フィールド)確認 | 
|\q|psqlを終了する|


# HomebrewでPosgreSQLをインストール

では、あらためてインストールから

```bash
% brew install postgresql
```

もしかしたらここでopensslに関するエラーや他のパッケージをreinstallしろ的な指示がでるかもしれないが、出たらその通りに従う

すでに何らかの事情でPostgreSQLをインストールしていて、最新版にupgradeしたらエラーで動かなくなった場合[こちら](http://qiita.com/blueplanet/items/58e511daa02b5684ec1d)を参照

### インストールできたか確認

以下、[コチラ](http://qiita.com/_daisuke/items/13996621cf51f835494b)のページから必要な項目をコピペ

```bash
% postgres --version
postgres(PostgreSQL) 9.3.4
```

# PostgreSQLサーバーの起動

起動しとかないとsql動かないし以下のコマンドも全部動かない

``` bash
% postgres -D /usr/local/var/postgres
LOG: database system was shut down at 2014-07-09 00:37:58 JST LOG: database system is ready to accept connections LOG: autovacuum launcher started
```

# その他設定

### サーバーが起動したか確認

（サーバーを立ち上げたウィンドウとは別のウィンドウを開く）

シェルに見た目の変化はないが、サーバーがバックで動いていればsqlコマンドが使えるようになっている
`psql -l`すると現在存在するデータベースの一覧が表示される

```bash
% psql -l 
                                  List of databases
    Name     |   Owner    | Encoding |   Collate   |    Ctype    | Access privileges
-------------+------------+----------+-------------+-------------+-------------------
 example-db  | demo_ysdyt | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 example-db2 | ysdyt      | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres    | ysdyt      | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0   | ysdyt      | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/ysdyt         +
             |            |          |             |             | ysdyt=CTc/ysdyt
 template1   | ysdyt      | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | ysdyt=CTc/ysdyt  +
             |            |          |             |             | =c/ysdyt
(5 rows)
```

### DBの置き場所を環境変数に設定

自分が使っているシェルに以下を追記してsourceで反映する

```bash
export PGDATA=/usr/local/var/postgres
```

### 新しいユーザアカウントを追加

環境変数$USER がスーパーユーザとして作成されており、そのまま使うのはあまり良くないので、新しくユーザを作成する。ユーザ作成時にパスワードを付与するためには-Pオプションを使用。

```bash
% createuser -P <pg-user>
Enter password for new role:
 Enter it again:
```

# データベースの作成

`createdb`コマンドで新規データベースを作成する。ここでは"example-db"というデータベースを作成した場合のコマンド。

```bash
% createdb example-db -O <pg-user>
```

オプション `-O`で作成するデータベースの利用者となるユーザーを指定
きちんと作成されていれば`psql -l`で作成したデータベースが表示されているはず


# psqlコマンドでDBに接続

```
% psql -U ユーザー名 example-db
psql(9.4.0) 
Type "help" for help 

example-db=>
```

`-U`オプションでデータベースに接続するユーザー名を指定する
インターフェイスが上記のような対話形式に切り替わる


この => の後にsqlのコマンドを打ってテーブルとか作る感じ（createdb, dropコマンドなど）
これにより“データベース”の下に複数の”テーブル”を作成する

sqlコマンドの入力は一行の終わりごとに `;`をつけるルール


# その他、sqlコマンド


| sqlコマンド | 説明 | 
|:-----------:|:------------:|
| \l   | DB一覧表示 | 
| \c DB名 | column |
| \d     |  テーブル一覧|
| \d テーブル名 | テーブルの項目(フィールド)確認 | 
|\q|psqlを終了する|


# psqlコマンドでテーブルの作成

参考項目を[コチラ](http://www.ne.jp/asahi/davinci/code/lapp/postgresql/index3.html)からコピペ


### PostgreSQLの項目型

```
    smailint / int2                     2 バイト整数
    integer / int / int4                4 バイト整数
    bigint / int8                       8 バイト整数
    decimal(a, a) / numeric(a, s)       10 進型
    real / float4                       6 桁単精度浮動小数点
    double precision / float8           15 桁倍精度浮動小数点
    serial                              4 バイト順序
    bigserial                           8 バイト順序
    date                                日付
    time                                時間
    timestamp                           日付時間
    char(文字数) / character            固定長文字列  (最大 4096 文字)
    varchar(文字数) / charcter varying  可変長文字列  (最大 4096 文字)
    text                                無制限テキスト
    ラージオブジェクト                  oid 型 (いまいちわからん) (^^;
    boolean / bool                      true / false
```

### テーブルの作成

基本的には以下の感じでテーブルを作成する

```
create table testm (
  key            char(008),
  data1          serial,
  data2          int8,
  data3          int8
);
```

これをsqlコマンドが使える状態（ example-db=> の後に）ベタで打っていく。;を打ってコードの終わりを明示しない限り次の行に移って入力続き待ちとなる。

新たにテーブルに追加項目を後から入れることも出来る（`alter`とか使う）。列名の変更も後からでもできる。

注意点として、PostgreSQL では、`primary key`、`unique`、`not null`制約については、 `create table`時にしか定義できない。

ちなみにテーブルを削除するときは下記（※ セミコロンを忘れない！）
`DROP TABLE <table名> ;`

### csvファイルをインポートしてデータベースを作成する

データベースをCSVでどこかから落としてきた時、それをimportしてテーブルを作る方法。
既存のcsvファイルからのimportには `COPY` というsqlコマンドを使う。importするときに各カラムの項目型を指定するのがめんどくさい。以下そのやり方。

csvデータのインポートに関しては基本は[ココ](http://symfoware.blog68.fc2.com/blog-entry-941.html)を参照

#### 1. インポートしたいデータベースに移動

```bash
$ psql -U ユーザー名 データベース名
```

#### 2. importしたいcsvデータの確認

例えば、こんな感じのデータをimportしてユーザ情報テーブルを作りたい場合

```csv
顧客番号,性別,年代,登録日,退会日,都道府県
2456,1,5_30代後半,2008-07-26,,宮城県
432,1,1_10代,2015-01-03,2016-05-03,群馬県
6688,2,5_30代後半,2015-01-28,,千葉県
```

#### 3. まずはCREATE TABLEする

`member`という名前のテーブルを作る

以下のテキストを => の後におもむろに貼り付ける 

```sql
CREATE TABLE member (
  member_id     INT,
  gender        INT,
  age_group     VARCHAR(20),
  registered_at DATE,
  withdrawn_at  DATE,
  prefecture    VARCHAR(20)
);
```

もしくは上記の`CREATE TABLE`文が書かれた member.sql スクリプトファイルを作成し、以下のpsqlコマンドでもテーブル作成できる

```
% psql -d データベース名 -U ユーザ名 -p ポート番号 -f member.sqlのパス
```

psqlコマンドを使う方法のほうが、CREATE TABLE文のスクリプトがファイルとしてきちんと残るし、シェルスクリプトを使って複数の.sqlファイルを実行して一気に複数テーブルをCREATEすることも出来るので便利

#### 4. csvデータを`COPY`で取り込む
同じく、以下のテキストを => の後におもむろに貼り付ける 

```sql
copy member
from 'csvファイルを置いてるパスを指定'
with csv
ignoreheader 1
;
GRANT ALL ON TABLE member TO public;
```

これで問題なければ `member`テーブルにcsvの内容が格納さえているはず。
GRANT ALL ~~~ は`member`テーブルに対するアクセス権限の付与について。後述。

####トラブルシューティング

以下のように super userじゃないと実行できないけど `\copy`でもいいよとでたらそれに従う。

```
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
```

もし`COPY`で取り込みたいcsvファイルのデータと`CREATE TABLE`時に指定した各ラベルのデータ型タイプが違っているとエラーを出して`COPY`が失敗する。

```
ERROR: invalid input syntax for integer: "カラム名"
```
型タイプがよくわからない場合はとりあえずデータ型を`text`にしとけばうまくいくっぽい…?


もし文字化けによるエラーが出た時。
EXCELで作成したCSVだとencodingは`Shift-JIS`になっている。エディタで変換して保存し直すか、nkfなどのlinuxコマンドで変換するか。もしくはsql上で以下を実行しても良い( Shift-JISに変換したい場合）

```sql
SET client_encoding TO 'SJIS';
```

#### テーブル名を変更したい場合

```
ALTER TABLE 名前変えたいテーブル RENAME TO 新しいテーブル前;
```

### テーブルをさわるための権限の（ユーザーに対する）追加・削除

参考は[こちら](http://www.dbonline.jp/postgresql/role/index3.html)

権限の付与は細かく設定できるらしいが、localで自分が使うだけだし、めんどうなので

```
GRANT ALL ON TABLE テーブル名 TO public;
```

で全体に全権限付けとけばとりあえずokなはず
個人に権限を付けたい場合は `public`のところにユーザーアカウント名を書く




# （おまけ）DataGrip（GUIツール）に接続してsql操作する

上記のようにコンソール上でsql操作するのもいいけど、個人的にはIDE（DB用のGUIツール）経由で操作するほうが楽で好き。
個人的お気に入りはJetBrain社のDataGripです。デザインもかっこいいので使っていて気持ちいい。

![DataGrip](https://www.xlsoft.com/jp/products/jetbrains/images/datagrip_query-console_dg.jpg)

画像は[こちら](https://www.xlsoft.com/jp/products/jetbrains/datagrip.html)より転載

[JetBrains DataGrip: Your Swiss Army Knife for Databases and SQL](https://www.jetbrains.com/datagrip/)



**注意点**

DataGripのようなツールからデータベースをいじるときでもターミナルでは

```
% postgres -D /usr/local/var/postgres
```
してsqlサーバーを立ち上げっぱなしにしないといけない（これが切れるとDataGrip上で ”コネクションが無いよ"とエラーがでる）


### データベース接続

1. DB接続画面で以下を入力
 
	- Host -> localhost
	- Port-> 5432
	- Database -> 上で作成した繋ぎたいデータベース名（※テーブル名ではない！）
	- User -> 上でデータベースを作成したユーザー名（アクセスの権限をもったユーザーであること。権限の付与などについては前述）
	- Password -> `create user`したときに設定したパスワード

2. その下の「Driver: PostgresSQL」というところをクリックして、その先の画面でdriverの「download」みたいなボタンを押すと自動でファイルが読み込まれる

3. 「Test Connection」押す（だめならここでコケる）

4. 「ok」をおす

その後、データベースと正しく接続されれば、

sqlを書くエディター画面（コンソール）の左のカラムに、設定したデータベースが表示される
(「pg_catalog」と「public」が表示される)
(正しく接続されていれば「public」の中の「tables」の中に作成したテーブルが表示されている。）