一意制約およびNOT NULL制約がある列に値が入っているとして、
その値を全体的に再編成したい場合がある。

でも、一つ一つ更新していたら、昔の値と重複エラーとなってしまったりして
めんどくさい。
NOT NULL制約がなければ一旦NULLにするって方法があるのだが。

んなわけで、一旦、ありえない値に更新してから、新しい値を更新する方法を考える。
例えば「1～99」の値を再編成する場合、
一旦「100～」とかの値に振り直してから、再度「1～99」の値を更新すれば、
昔の値に影響されることなく(重複エラーが発生することなく)
更新ができるというわけだ。

まず考えたのは、最大値に+1していけばいいんじゃないかという案。
┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│   SET 列 = (SELECT MAX(列) FROM 表) + 1;                                   │
└──────────────────────────────────────┘
しかし、これは都度最大値を計算してくれるわけではなく、
最初の一回しか計算してくれないから例えば全件「100」とかになって
うまくいかない(更新できない)。

これを実現するにはこんなSQL文となる。
┌──────────────────────────────────────┐
│WITH 一時表(キー,                                                           │
│            列) AS (                                                        │
│         SELECT キー,                                                       │
│                (SELECT max(列) FROM 表) + 100                  --最大+100  │
│              + row_number() OVER()                             --連番になる│
│             FROM 表                                                        │
│     )                                                                      │
│UPDATE 表                                                                   │
│    SET 列 = 一時表.列                                                      │
│    FROM 一時表                                                             │
│    WHERE 一時表.キー = 表.キー;                                            │
└──────────────────────────────────────┘
これなら確実だけど、やや複雑だ。

いっそのこと、乱数に頼った方がシンプルかも。
┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│   SET 列 = 100 + ROUND((RANDOM() * 1000)::numeric, 0);                     │
└──────────────────────────────────────┘
稀に、重複するかもしれないが、その場合は、再度実行するか、
RANDOM()に掛ける値を広げるとよい。

空欄もNULLも見た目が一緒だから、まとめてしまえ！

ということで、まずは空欄にまとめる方法。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM (SELECT 1 AS "k",'a'   AS "c"  --文字                              │
│    UNION SELECT 2 AS "k",''    AS "c"  --空欄                              │
│    UNION SELECT 3 AS "k",NULL  AS "c"  --NULL                              │
│    ) AS "t"                                                                │
│    ORDER BY COALESCE("c", '') ASC;                                         │
└──────────────────────────────────────┘
「空欄→NULL→文字」の順になる。
ちなにみ四番目に「空欄」を追加してみたが「空欄→空欄→NULL→文字」の順となった。
ま、「空欄→NULL」の部分はすべて等価なので、
第二ソート指定で、任意の順に並び替えるってことになるだろう。

続いてNULLの方にまとめる方法。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM (SELECT 1 AS "k",'a'   AS "c"  --文字                              │
│    UNION SELECT 2 AS "k",''    AS "c"  --空欄                              │
│    UNION SELECT 3 AS "k",NULL  AS "c"  --NULL                              │
│    ) AS "t"                                                                │
│    ORDER BY CASE WHEN LENGTH("c") > 0 THEN "c" ELSE NULL END ASC;          │
└──────────────────────────────────────┘
「文字→空欄→NULL」の順になる。
ちなにみ四番目に「空欄」を追加してみたが「文字→空欄→空欄→NULL」の順となった。
なにやら見えない「空欄→NULL」があるみたい。
といっても、同じ値(NULL)を並び替えても、どうなるか保証されない訳だから、
前述の通りあまり気にしなくてよかろう。

いつも末尾になる「NULL」を先頭にもってきたい場合は、
「IS NOT NULL ASC」もしくは「IS NULL DESC」を最初に挟めばいい。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM (SELECT 1 AS "k",'a'   AS "c"  --文字                              │
│    UNION SELECT 2 AS "k",''    AS "c"  --空欄                              │
│    UNION SELECT 3 AS "k",NULL  AS "c"  --NULL                              │
│    ) AS "t"                                                                │
│    ORDER BY "c" IS NOT NULL ASC,                                           │
│             "c"             ASC;                                           │
└──────────────────────────────────────┘
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM (SELECT 1 AS "k",'a'   AS "c"  --文字                              │
│    UNION SELECT 2 AS "k",''    AS "c"  --空欄                              │
│    UNION SELECT 3 AS "k",NULL  AS "c"  --NULL                              │
│    ) AS "t"                                                                │
│    ORDER BY "c" IS NULL DESC,                                              │
│             "c"         ASC;                                               │
└──────────────────────────────────────┘
これで「NULL→空欄→文字」となる。

論理型のソート順は「偽→真」なので、NULLと反対の条件にして
NULLだけを偽にしてしまえば、NULLが先頭にくる(ややこしい)という仕組みだ。

空欄とNULLのソート順もあやふやなので試してみた。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM (SELECT 1 AS "k",'a'   AS "c"  --文字                              │
│    UNION SELECT 2 AS "k",''    AS "c"  --空欄                              │
│    UNION SELECT 3 AS "k",NULL  AS "c"  --NULL                              │
│    ) AS "t"                                                                │
│    ORDER BY "c" ASC;                                                       │
└──────────────────────────────────────┘

  昇順：「空欄→文字→NULL」の順
  降順：「NULL→文字→空欄」の順(昇順の真逆)

という結果となった。

空欄とNULLの区別がぱっとみつかないので、
なんで空欄が先頭と末尾に固まるんだろうという罠にハマってしまいそうだ。

文字数を取得したい場合には「LENGTH()」を使う。
┌──────────────────────────────────────┐
│SELECT LENGTH('abcあい');                                                →5│
└──────────────────────────────────────┘

文字長を取得したい場合には「OCTET_LENGTH()」を使う。
┌──────────────────────────────────────┐
│SELECT OCTET_LENGTH('abcあい');                                          →9│
└──────────────────────────────────────┘
「UTF-8」環境で実行したので、上記だと「1＋1＋1＋3＋3」で「9」となる。

論理型をソートするとどの順に並ぶのかあやふやなので以下で実験してみた。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM (SELECT TRUE  AS "c"                                               │
│    UNION SELECT FALSE AS "c"                                               │
│    UNION SELECT NULL  AS "c"                                               │
│    ) AS "t"                                                                │
│    ORDER BY "c" ASC;                                                       │
└──────────────────────────────────────┘
「ASC」(昇順)を「DESC」(降順)に置換したりして試したところ…。

  昇順：「FALSE→TRUE→NULL」の順
  降順：「NULL→TRUE→FALSE」の順(昇順の真逆)

という結果となった。

なんか、「TRUE→FALSE」が正順のように錯覚してたなぁ。

ちなみに、「UNION ALL」にして複数件ぐちゃぐちゃにして試してみたが、
上記の結果に従った結果となった(「FALSE…FALSE→TRUE…TRUE→」みたいな感じ)。

ちなみに、どれが根拠になってるんだろう(わからん)。
  ・FALSE   ＜ TRUE
  ・'false' ＜ 'true'
  ・'f'     ＜ 't'
  ・'no'    ＜ 'yes'
  ・'n'     ＜ 'y'
  ・0       ＜ 1
どちらにしろ、FALSEの方が前だわな。

「serial型」もしくは「bigserial型」の列(例えばIDなど)において、
レコードを削除するなどして、歯抜けが発生した場合、
前詰にしたくなる場合がある。

以下がそのやり方。
┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│    SET 列 = (SELECT COUNT(*)                                               │
│                  FROM 表 t1,                                               │
│                       表 t2                                                │
│                  WHERE t1.列 >= t2.列                                      │
│                    AND t1.列  = 表.列                                      │
│                  GROUP BY t1.列);                                          │
├──────────────────────────────────────┤
│SELECT SETVAL ('AutoNumber用列名', (SELECT COUNT(*) FROM 表), true);        │
└──────────────────────────────────────┘

┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│    SET 出力列 = TO_NUMBER(REPLACE(入力列, 'NO', ''), '000')                │
│    WHERE 入力列 LIKE '%NO%';                                               │
└──────────────────────────────────────┘
「NO1、NO2、NO10…、NO999」とかのように先頭に文字が付いている連番から
数値のみを抜き出すUPDATE文。
「REPLACE()」で置換し、「TO_NUMBER()」で数値変換といった流れだ。

PostgreSQLでも配列っていう型があるが、その中に特定の値が含まれているか
判定するには？

PHPでいうところの「in_array()」みたいなやつ。

基本的には、これ。
┌──────────────────────────────────────┐
│配列 && ARRAY[値]                                                           │
└──────────────────────────────────────┘
ただ、これだと配列が空っぽの場合はfalseではなくNULL扱いとなる

まあ、true(falseとnull以外)だけ判定する分には問題ないけど、
厳密にしたい場合は以下のような一手間を加える。
┌──────────────────────────────────────┐
│COALESCE(配列 && ARRAY[値], false)                                          │
└──────────────────────────────────────┘

これだとtrue以外を以下で判定できる。
┌──────────────────────────────────────┐
│NOT COALESCE(配列 && ARRAY[値], false)                                      │
└──────────────────────────────────────┘

ちなみに、以下のように複数の指定も可能。
┌──────────────────────────────────────┐
│配列 && ARRAY[値,値]                                                        │
└──────────────────────────────────────┘
どれかを含んでいればtrueとなる。

より実用的な複合版は以下のような感じ。
┌──────────────────────────────────────┐
│SELECT ROW_NUMBER() OVER(PARTITION BY 列,列 ORDER BY 列 ASC,列 ASC) FROM 表;│
└──────────────────────────────────────┘
「PARTITION BY」でグルーピングして、その中で「ORDER BY」で並び替える。
グルーピングは複合条件も可能で、並び替えも複合条件が可能。
上記は二件までの例だが、もちろ三件以上も可能だ。