PostgreSQL を使う

2019年8月8日

はじめに

PostgreSQL を使ってみる。

環境

  • Ubuntu 16.04 LTS
  • PostgreSQL 9.5.16

セットアップ

インストール。

$ sudo apt-get install postgresql

クライアントの実行。

$ psql
psql: FATAL:  role "xxxx" does not exist

権限がないので扱えない。postgres というユーザーが設定の権限を持っているので、それで操作する。

postgres ユーザーに変更

$ sudo su - postgres

クライアントの実行。

$ psql
...
postgres=#

ロールの確認。

=# \du

ロールの作成。

=# create role xxxx login createdb password 'xxxxxxxx';

データベースを操作するユーザーと同じ名前のロールを作成する。

データベースの確認。

=# \l

データベースの作成。

=# create database xxxx owner xxxx;

操作するユーザーと同じ名前のデータベースを作成する。

終了。

=# \q

もとのユーザーに戻って、psql を実行できるか確認する。

テーブルの作成

ここではもとのユーザーで操作する。

テーブルの確認。

=> \z

テーブルの作成。

=> create table sample (
(> id serial,
(> name text
(> );

"sample" という名前のテーブルを作成する。"id"、"name" というカラムを持つものとする。serial タイプは自動連番。

テーブル定義の確認。

=> \d sample

データの追加。

=> insert into sample
-> (name)
-> values
-> ('apple');

データの表示。

=> select * from sample;

カラムの追加

=> alter table sample add column num integer;

integer タイプの "num" というカラムを追加。

データの更新

=> update sample set num = 1 where name = 'apple';

CSV ファイルからインポート

=> \copy sample (id,name,num) from 'fruits.csv' with csv;

CSV ファイルへエクスポート

=> \copy sample to 'sample.csv' with csv;

データの削除

=> delete from sample;

テーブルの削除

=> drop table sample;

検索

日付タイプ date で日付を扱える。

=> create table sample (
(> date date,
(> keyword text
(> );

日付検索

一致

=> select * from sample where date = '2018-02-11';

以外

=> select * from sample where date <> '2018-02-11';

以降

=> select * from sample where date > '2018-01-01';

範囲

=> select * from sample where date between '2018-01-01' and '2018-12-31';

日付の部分の取り出し

=> select * from sample where date_part('year', data) = '2018';

今日

=> select * from sample where date = current_date;

文字列の部分一致

=> select * from sample where keyword like '%aaa%';

データの出現数ランキング

=> select keyword, count(keyword) from sample group by keyword order by count desc;

その他

リモートサーバーへの接続

リモートにある PostgreSQL サーバーに接続するには次のようにする。

$ psql --host=mydbhost --port=5432 --username=mydbuser --dbname=mydbuser

パスワードの入力を省略したい場合は、環境変数 PGPASSWORD にパスワードを設定する。

スクリプトファイルの実行

PostgreSQL のコマンドが書かれたファイルを実行するには、次のようにする。

$ psql -f script.sql