Help us understand the problem. What is going on with this article?

PostgreSQLの環境をmacのローカルに作成&SQL操作(&ついでにDataGripへの接続)

More than 3 years have passed since last update.

主に使うコマンドまとめ

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

サーバーの起動

% 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 keyuniquenot 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です。デザインもかっこいいので使っていて気持ちいい。

DataGrip

画像はこちらより転載

JetBrains DataGrip: Your Swiss Army Knife for Databases and SQL

注意点

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

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

してsqlサーバーを立ち上げっぱなしにしないといけない(これが切れるとDataGrip上で ”コネクションが無いよ"とエラーがでる)

データベース接続

  1. DB接続画面で以下を入力
- Host -> localhost
- Port-> 5432
- Database -> 上で作成した繋ぎたいデータベース名(※テーブル名ではない!)
- User -> 上でデータベースを作成したユーザー名(アクセスの権限をもったユーザーであること。権限の付与などについては前述)
- Password -> `create user`したときに設定したパスワード
  1. その下の「Driver: PostgresSQL」というところをクリックして、その先の画面でdriverの「download」みたいなボタンを押すと自動でファイルが読み込まれる

  2. 「Test Connection」押す(だめならここでコケる)

  3. 「ok」をおす

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

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

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away