「VACUUM」というのは簡単にいえば「お掃除係」。

┌──────────────────────────────────────┐
│VACUUM;                                                                     │
└──────────────────────────────────────┘
単なる「VACUUM」は、ゴミ領域のお掃除をしてくれるだけ。(なので排他とか関係ない)

┌──────────────────────────────────────┐
│VACUUM FULL;                                                                │
└──────────────────────────────────────┘
「VACUUM FULL」は、お掃除に加えて整理整頓をしてくれる。よって排他がかかる。
つまり、効果は大きいが、遅い・場所をとる・占有するっていう弊害がある。
やるんなら、覚悟してやるってことだね。

┌──────────────────────────────────────┐
│VACUUM 表;                                                                  │
└──────────────────────────────────────┘
表を指定すればその表だけを対象にできる。

┌──────────────────────────────────────┐
│VACUUM VERBOSE;                                                             │
└──────────────────────────────────────┘
「VERBOSE」を付けると進行状況を報告してくれる。

┌──────────────────────────────────────┐
│VACUUM ANALYZE;
└──────────────────────────────────────┘
「ANALYZE」を付けると、「ANALYZE」もやってくれる。

ん？「ANALYZE」って何だ？

ということで、次回へ続く。

「COPY」句は、結果をファイルとして出力してくれる便利な命令。
出力先ファイルは上書されるので注意。

テーブルをバイナリファイル形式で出力する。
┌──────────────────────────────────────┐
│COPY BINARY 表 TO 'C:\表.bin';                                              │
└──────────────────────────────────────┘

テーブルをTSVファイル形式で出力する。
┌──────────────────────────────────────┐
│COPY 表 TO 'C:\表.tsv';                                                     │
└──────────────────────────────────────┘

テーブルをCSVファイル形式で出力する。
┌──────────────────────────────────────┐
│COPY 表 TO 'C:\表.csv' WITH CSV;                                            │
└──────────────────────────────────────┘

任意の区切文字を指定して出力する。(以下の例では「|」を区切文字として指定)
┌──────────────────────────────────────┐
│COPY 表 TO 'C:\表.txt' USING DELIMITERS '|';                                │
└──────────────────────────────────────┘
区切文字は1バイト文字しかダメなようです。

NULL文字を置換する。(以下の例ではNULL文字を「？？」に置換するよう指定)
┌──────────────────────────────────────┐
│COPY 表 TO 'C:\表.txt' WITH NULL AS '？？';                                 │
└──────────────────────────────────────┘
こちらはなんでもOKのようです。

文字コードを指定して出力する。(以下の例では「Shift_JIS」を指定)
┌──────────────────────────────────────┐
│COPY 表 TO 'C:\表.txt' WITH encoding 'SJIS';                                │
└──────────────────────────────────────┘

SQLの結果も出力できます。(っていうか、こっちが本題っぽい)
┌──────────────────────────────────────┐
│COPY (SELECT NOW()) TO 'C:\表.txt';                                         │
└──────────────────────────────────────┘

「AVG()」は平均を計算する集合関数。
┌──────────────────────────────────────┐
│SELECT avg("c") FROM (VALUES (1),(2),(3)) AS "t"("c");                   →2│
└──────────────────────────────────────┘
「(1＋2＋3)÷3＝2」となる。

では、母集団にNULLが混じっていた場合には？
┌──────────────────────────────────────┐
│SELECT avg("c") FROM (VALUES (1),(2),(NULL)) AS "t"("c");              →1.5│
└──────────────────────────────────────┘
NULLは無視される。で、「(1＋2)÷2＝1.5」となる。

ではでは、母集団がすべてNULLだった場合は？
┌──────────────────────────────────────┐
│SELECT avg("c"::numeric)                                                    │
│    FROM (VALUES (NULL),(NULL),(NULL)) AS "t"("c");                   →NULL│
└──────────────────────────────────────┘
「NULL」になる

ついでに、母集団がなかった場合には？
┌──────────────────────────────────────┐
│SELECT avg("c") FROM (VALUES (1),(2),(3)) AS "t"("c") WHERE FALSE;    →NULL│
└──────────────────────────────────────┘
これも「NULL」になる。

集合関数は、とにかく結果を一つ返して、
値を返すのが無理そうな時はNULLを返すって感じか。

ついでに、グループごとの最大値もしくは最小値を含む明細を抽出する場合に
NULLを最劣後にするSQL文も記録にとどめておこう。

まずは最小値を含む明細を求める方法。
┌──────────────────────────────────────┐
│SELECT DISTINCT ON ("k") "k","c","s"                                        │
│    FROM (VALUES ('g',1,'a'),('g',2,'b'),('g',NULL,'c')) AS "t"("k","c","s")│
│    ORDER BY "k"         ASC,                                               │
│             "c" IS NULL ASC,                                               │
│             "c"         ASC;                                      →(g,1,a)│
└──────────────────────────────────────┘

次に最大値を含む明細を求める方法。
┌──────────────────────────────────────┐
│SELECT DISTINCT ON ("k") "k","c","s"                                        │
│    FROM (VALUES ('g',1,'a'),('g',2,'b'),('g',NULL,'c')) AS "t"("k","c","s")│
│    ORDER BY "k"         ASC,                                               │
│             "c" IS NULL ASC,                                               │
│             "c"         DESC;                                     →(g,2,b)│
└──────────────────────────────────────┘

つまり、最後のORDERだけ逆(ASC←→DESC)にすればよいだけ。
ちなみに、「"k" ASC」は疑似「GROUP BY」なので、「"k" DESC」でもOK。
ついでに「"c" IS NULL ASC」は「"c" IS NOT NULL ASC」か「"c" IS NULL DESC」に
すれば、NULLが先頭にくる。

レコード上にNULLが混じっていた場合どうなるのか、
以下のサンプルデータを元におさらいしてみよう。
┌──────────────────────────────────────┐
│SELECT "c" FROM (VALUES (1),(2),(NULL)) AS "t"("c");            →1、2、NULL│
└──────────────────────────────────────┘

まずは、集合関数について。
┌──────────────────────────────────────┐
│SELECT COUNT("c") FROM (VALUES (1),(2),(NULL)) AS t("c");                →2│
│SELECT MAX("c")   FROM (VALUES (1),(2),(NULL)) AS t("c");                →2│
│SELECT MIN("c")   FROM (VALUES (1),(2),(NULL)) AS t("c");                →1│
│SELECT AVG("c")   FROM (VALUES (1),(2),(NULL)) AS t("c");              →1.5│
└──────────────────────────────────────┘
NULLはガン無視されるということだね。

次は並び順について。
┌──────────────────────────────────────┐
│SELECT "c"                                                                  │
│    FROM (VALUES (1),(2),(NULL)) AS "t"("c")                                │
│    ORDER BY "c" ASC;                                           →1、2、NULL│
│SELECT "c"                                                                  │
│    FROM (VALUES (1),(2),(NULL)) AS "t"("c")                                │
│    ORDER BY "c" DESC;                                          →NULL、2、1│
└──────────────────────────────────────┘
ただし、現状はこうですよって感じで、この順は保証されてないっぽい。

ま、動いてんだから…ってのはよくないですよね。
じゃ、NULLを厳密に管理するには？
┌──────────────────────────────────────┐
│SELECT "c"                                                                  │
│    FROM (VALUES (1),(2),(NULL)) AS "t"("c")                                │
│    ORDER BY "c" IS NULL ASC,                                               │
│             "c"         ASC;                                   →1、2、NULL│
│SELECT "c"                                                                  │
│    FROM (VALUES (1),(2),(NULL)) AS "t"("c")                                │
│    ORDER BY "c" IS NULL DESC,                                              │
│             "c" DESC;                                          →NULL、2、1│
└──────────────────────────────────────┘
並び順に「IS NULL」をつけてやればよい。

「ARRAY_AGG()」では配列をさらに配列として集約するのはできないのだろうか。
┌──────────────────────────────────────┐
│SELECT ARRAY_AGG(c) FROM (VALUES (ARRAY[1,2]),(ARRAY[3,4])) AS t(c);        │
└──────────────────────────────────────┘
ってやると以下のエラーがでる。(integer型に限った話ではなくtext型も)
「ERROR:  could not find array type for data type integer[]」

┌──────────────────────────────────────┐
│SELECT ARRAY_AGG(ROW(c)) FROM (VALUES (ARRAY[1,2]),(ARRAY[3,4])) AS t(c);   │
└──────────────────────────────────────┘
ROW式ってやつを使って見ると「{"(\"{1,2}\")","(\"{3,4}\")"}」という結果になる。

┌──────────────────────────────────────┐
│SELECT ARRAY_TO_STRING(ARRAY_AGG(ROW(c)),',')                               │
│    FROM (VALUES (ARRAY[1,2]),(ARRAY[3,4])) AS t(c);                        │
└──────────────────────────────────────┘
で、文字列に変換するとこうなる。「("{1,2}"),("{3,4}")」

配列を配列として集約、多次元配列的になるようだ。

ただし、求めたいのは「{1,2,3,4}」という結果。
一次元の配列にする方法はあるのだろうか？

ま、代案としては、CSV形式の文字列とかにして、最後に配列にするとかか。

min()とmax()は行を跨いで、最小値および最大値を取得できるわけだが…。
┌──────────────────────────────────────┐
│SELECT min(列) FROM 表;                                                     │
│SELECT max(列) FROM 表;                                                     │
└──────────────────────────────────────┘

列を跨いで、最小値および最大値を取得する方法がある。これだ。
┌──────────────────────────────────────┐
│SELECT least(列,列…);                                                      │
│SELECT greatest(列,列…);                                                   │
└──────────────────────────────────────┘

こんな感じになる。
┌──────────────────────────────────────┐
│SELECT least(1,2,3);                                                     →1│
│SELECT greatest(1,2,3);                                                  →3│
└──────────────────────────────────────┘

AS演算子で別名にすりゃいい。
┌──────────────────────────────────────┐
│SELECT (SELECT array_agg(t1.c1 || '-' || t2.c1)                             │
│            FROM generate_series(1, 2, 1) AS t2(c1))                        │
│    FROM generate_series(1, 3, 1) AS t1(c1);                                │
└──────────────────────────────────────┘

但し、表名だけを別名にしてもダメ(列名も必要)だから注意。
┌──────────────────────────────────────┐
│SELECT (SELECT array_agg(t1.generate_series || '-' || t2.generate_series)   │
│            FROM generate_series(1, 2, 1) AS t2)                            │
│    FROM generate_series(1, 3, 1) AS t1;                                    │
└──────────────────────────────────────┘

┌──────────────────────────────────────┐
│(VALUES(値,値),(値,値)) AS 表(列,列)                                        │
└──────────────────────────────────────┘
上記のような書式で一時的な疑似テーブルをつくることができる。

以下のような感じで使える。
┌──────────────────────────────────────┐
│SELECT * FROM (VALUES(1,'a'),(2,'b')) AS t(c1,c2);                          │
└──────────────────────────────────────┘

制約とかをまとめてみた。
┌──────┬──────┬──────┬─┬─┬─────────────┐
│    分類    │            │ キーワード │表│列│         関連事項         │
├──────┼──────┼──────┼─┼─┼─────────────┤
│既定値      │既定値      │DEFAULT     │－│○│                          │
├──────┼──────┼──────┼─┼─┼─────────────┤
│            │検査制約    │CHECK       │○│△│                          │
│            ├──────┼──────┼─┼─┼─────────────┤
│制約        │非NULL制約  │NOT NULL    │－│○│PRIMARY KEY               │
│            ├──────┼──────┼─┼─┼─────────────┤
│            │一意性制約  │UNIQUE      │○│△│PRIMARY KEY、UNIQUE INDEX │
├──────┼──────┼──────┼─┼─┼─────────────┤
│            │主キー制約  │PRIMARY KEY │○│－│                          │
│キー        ├──────┼──────┼─┼─┼─────────────┤
│            │外部キー制約│FOREIGN KEY │○│－│                          │
├──────┼──────┼──────┼─┼─┼─────────────┤
│インデックス│インデックス│INDEX       │○│－│PRIMARY KEY、UNIQUE INDEX │
└──────┴──────┴──────┴─┴─┴─────────────┘