主に使うコマンドまとめ
一番最初に、今後よく使うコマンドをまとめておく
サーバーの起動
% postgres -D /usr/local/var/postgres
このコマンドでpostgresサーバを起動し、起動させたら別のコンソールウィンドウを開いてpsqlコマンドを操作する。
データベース一覧を見る
% psql -l
データベースに接続
% psql -U ユーザー名 データベース名
その他、sqlコマンド系
| sqlコマンド | 説明 |
|---|---|
| \l | DB一覧表示 |
| \c DB名 | 接続先DB変更 |
| \d | テーブル一覧 |
| \d テーブル名 | テーブルの項目(フィールド)確認 |
| \q | psqlを終了する |
HomebrewでPosgreSQLをインストール
では、あらためてインストールから
% brew install postgresql
もしかしたらここでopensslに関するエラーや他のパッケージをreinstallしろ的な指示がでるかもしれないが、出たらその通りに従う
すでに何らかの事情でPostgreSQLをインストールしていて、最新版にupgradeしたらエラーで動かなくなった場合こちらを参照
インストールできたか確認
以下、コチラのページから必要な項目をコピペ
% postgres --version
postgres(PostgreSQL) 9.3.4
PostgreSQLサーバーの起動
起動しとかないとsql動かないし以下のコマンドも全部動かない
% 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すると現在存在するデータベースの一覧が表示される
% 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で反映する
export PGDATA=/usr/local/var/postgres
新しいユーザアカウントを追加
環境変数$USER がスーパーユーザとして作成されており、そのまま使うのはあまり良くないので、新しくユーザを作成する。ユーザ作成時にパスワードを付与するためには-Pオプションを使用。
% createuser -P <pg-user>
Enter password for new role:
Enter it again:
データベースの作成
createdbコマンドで新規データベースを作成する。ここでは"example-db"というデータベースを作成した場合のコマンド。
% 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コマンドでテーブルの作成
参考項目をコチラからコピペ
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データのインポートに関しては基本はココを参照
1. インポートしたいデータベースに移動
$ psql -U ユーザー名 データベース名
2. importしたいcsvデータの確認
例えば、こんな感じのデータをimportしてユーザ情報テーブルを作りたい場合
顧客番号,性別,年代,登録日,退会日,都道府県
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という名前のテーブルを作る
以下のテキストを => の後におもむろに貼り付ける
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で取り込む
同じく、以下のテキストを => の後におもむろに貼り付ける
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に変換したい場合）
SET client_encoding TO 'SJIS';
テーブル名を変更したい場合
ALTER TABLE 名前変えたいテーブル RENAME TO 新しいテーブル前;
テーブルをさわるための権限の（ユーザーに対する）追加・削除
参考はこちら
権限の付与は細かく設定できるらしいが、localで自分が使うだけだし、めんどうなので
GRANT ALL ON TABLE テーブル名 TO public;
で全体に全権限付けとけばとりあえずokなはず
個人に権限を付けたい場合は publicのところにユーザーアカウント名を書く
（おまけ）DataGrip（GUIツール）に接続してsql操作する
上記のようにコンソール上でsql操作するのもいいけど、個人的にはIDE（DB用のGUIツール）経由で操作するほうが楽で好き。
個人的お気に入りはJetBrain社のDataGripです。デザインもかっこいいので使っていて気持ちいい。
画像はこちらより転載
JetBrains DataGrip: Your Swiss Army Knife for Databases and SQL
注意点
DataGripのようなツールからデータベースをいじるときでもターミナルでは
% postgres -D /usr/local/var/postgres
してsqlサーバーを立ち上げっぱなしにしないといけない（これが切れるとDataGrip上で ”コネクションが無いよ"とエラーがでる）
データベース接続
- DB接続画面で以下を入力
- Host -> localhost
- Port-> 5432
- Database -> 上で作成した繋ぎたいデータベース名（※テーブル名ではない！）
- User -> 上でデータベースを作成したユーザー名（アクセスの権限をもったユーザーであること。権限の付与などについては前述）
- Password -> `create user`したときに設定したパスワード
その下の「Driver: PostgresSQL」というところをクリックして、その先の画面でdriverの「download」みたいなボタンを押すと自動でファイルが読み込まれる
「Test Connection」押す（だめならここでコケる）
「ok」をおす
その後、データベースと正しく接続されれば、
sqlを書くエディター画面（コンソール）の左のカラムに、設定したデータベースが表示される
(「pg_catalog」と「public」が表示される)
(正しく接続されていれば「public」の中の「tables」の中に作成したテーブルが表示されている。）