あまり進展しないPostgre SQLメモ -case式

達人に学ぶ SQL徹底指南書を読んで、Postgre SQL DBで試してみました。

---

メモ

とりあえずpostgresql serverの起動

$ postgres -D /usr/local/var/postgres

いるはずのユーザのログインで、DBがないって怒られる

(usr) $ psql -U usr -W
Password for user usr: 
FATAL:  database "usr" does not exist
psql: FATAL:  database "usr" does not exist
$ 

なにやら、psql実行時ユーザと同じロール名でアクセスする際は、
-UをつけていてもDB名として認識されるみたいでした。
DB名を記載すれば、期待通り、-Uはロール名として認識してくれました。

$ psql -U usr -W testdb

\l

DBの一覧表示

# create database professional_sql ;

databaseの作成

# \c proffesional_sql

DBへの接続

新規テーブルの作成

create table Greatests (
  key char(1) primary key
  , x integer not null
  , y integer not null
  , z integer not null
);

\dt

テーブルの一覧表示

\i file

SQLファイルからクエリの実行

UNION / UNION ALL

UNIONは重複を削除
UNION ALLは削除しない

\w ファイル名

バッファに残っている内容をファイルに保存

PostgresqlでGreatest/Least関数使えるんですね

Release 8.1で追加されたみたいでした。

Add GREATEST() and LEAST() variadic functions (Pavel Stehule)
8.1 release note

都道府県テーブルを作る

東京都の人口が1,300万なので、
２バイトのsmallintでは足りず、４バイトのintegerがちょうど良さげ

最初に該当したwhen句を返す

対象値は重複させないようにする

ELSE句を省略すると

ELSE NULLとなる

条件法

「PならばQ」の論理式
「P→Q」と書ける

論理積(logical product)

「PかつQ」の論理式 「P^Q」と書ける

\r

クエリのキャンセル

テーブルのリネーム

alter table cousemaster rename to coursemaster;

# \! pwd

psqlプロンプトでlinuxコマンドを実行

# \d TABLE_NAME

テーブル定義を確認する
参考：PostgreSQLのpsqlでテーブル定義を確認する方法

テーブルに付く制約とカラムに付く制約

表現が正しいか自信がないが、あるカラムに対する制約はカラムに適用され、
複数カラムにまたがる制約はテーブルに適用されているみたいでした。
alter文やテーブル定義を確認しただけの推測です。公式ドキュメント読まな。。。

# alter table studentclub add primary key (std_id, club_id);
# alter table studentclub alter column main_club_flg set not null;

アルファにしてオメガ

最初にして最後
コトバンクより

$createdb hoge / $dropdb hoge

localのpostgresに対してcreate database hoge(drop database hoge)を実行する

データベース名

先頭はアルファベット
63文字以内
ユーザ名と同じデータベース名を推奨

DBに接続するPostgreSQLユーザ

指定がなければ、OSのアカウントと同じ名前を使用
Postgresを起動するOSユーザは、PostgreSQLユーザとしても存在する

mydb=> / mydb=#

mydb=> 一般ユーザ
mydb=# スーパーユーザ

SELECT version();

バージョン情報を取得

識別子を二重引用符で囲むと大文字小文字を区別する

公式ドキュメントを読んでてびっくりした。

SQLはキーワードと識別子に対して大文字小文字を区別しません。 
ただし、（上では行っていませんが）識別子が二重引用符で括られていた場合は大文字小文字を区別します。

version 8.4.4だけど、多分、最新でもそうでしょう。

COPYコマンド

クライアントとサーバが別で(localhostでなくて)、サーバにファイルを置かない場合、\copyコマンド
サーバにファイルを置く場合(COPYコマンド)
参照：CSVファイルをデータベースにインポートする

# copy coursemaster from '/Users/usr/test.csv' USING DELIMITERS ',';

from句でファイルをフルパスで書く必要性がありました。

ビュー

他のビューに対して、ビューをつくってもOK

CREATE VIEW tempview AS
    SELECT city, location
        FROM weather, cities
        WHERE city = name;

ウィンドウ関数

チュートリアルで説明されるほど、メジャーな関数なのですが、
自分がOracle DB Bronze SQLを勉強したときは、試験範囲ではなかったと思います。
利点は、集約しなくても、平均や順位を出すことができるということです。

avg(salary) OVER (PARTITION BY depname)

rank() OVER (PARTITION BY depname ORDER BY salary DESC)

OVER句をつけることにより、avg集約関数ではなく、ウィンドウ関数として扱われます。

また、partition by COLUMN_NAMEは省略可能で、この場合は全ての行を含む仮想テーブルを対象とします。

SELECT score, sum(score) over () from score_table;

また、ORDER BY句のみの場合は、
ウィンドウ範囲が広がっていくような動きになります。

SELECT score, sum(score) over (ORDER BY score) from score_table;

公式にもありますが、重複するscoreがsumに含まれないことに注意が必要です。 ウィンドウ関数は、SELECT句とORDER BY句でしか使えません

テーブルの継承

初めて知った。テーブルを拡張する場合に、選択可能な手段です。
デメリットはUNIQUE制約または外部キーと合わせて使えないこと。これは痛い

CREATE TABLE users (
  name text,
  create_dt date
);

CREATE TABLE super_users (
  approver text
) INHERITS (users);

文字列定数内に'シングルクォートを入れる

''と続けて2つシングルクォートを入力する

改行で文字定数を区切った場合

SELECT 'foo'
'bar';
 ?column? 
----------
 foobar

SELECT 'foo''bar';
 ?column? 
----------
 foo'bar

# SELECT 'foo'      'bar';
ERROR:  syntax error at or near "'bar'" at character 19
STATEMENT:  SELECT 'foo'      'bar';
ERROR:  syntax error at or near "'bar'"
LINE 1: SELECT 'foo'      'bar';
                          ^

エスケープ文字列定数

E'hoge'というように、シングルクォートの前にEをつける
エスケープ文字列定数内だとシングルクォートを入力するのに、''以外に\'も使える

引用符を指定する

$$や$hoge$と文字列定数の区切り文字を指定することができる

Oracle DBでは別名にasをつけない

トリビア

dropテーブルの権限は付与できない

テーブルの所有者しか、テーブルの削除はできない。権限をGRANTできない。
レコードの削除(DELETE)権限は付与することができる

PostgreSQLはレコードをDELETEしても、物理削除しない

知らなかったよー。

以下引用：OSS DB/Silverの例題解説「運用管理 - 基本的な運用管理作業(自動バキュームの概念と動作)」

PostgreSQLは追記型のデータ構造を採っており、
データをDELETEしても、データに削除済フラグがつくだけで、
物理的にはデータが削除されません。

UPDATEも内部的にはDELETEとINSERTを実行するため、
古いデータが残り、そのまま放置すると物理的なデータ量が増えていきます。
この削除済みデータの領域を回収するのがバキューム(VACUUM)機能であり、
それを自動的に実行するのが自動バキュームです。

PITR

Point In Time Recovery
特定時点への復旧のこと

createuserの方式変更

PostgreSQL 9.1:対話式
PostgreSQL 9.2:デフォルトで権限を付与しない

付与していないユーザ権限の剥奪

エラーにもならず、コマンドは実行される

RoleとUserに違いがない

PostgreSQLだとそうらしい