PostgreSQLで特定カラムだけ重複なしに選択するにはDISTINCT ONを使う

  • PostgreSQL
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

表Aと表BをJOINして、表Aの特定カラムを重複なしに選択し、別テーブルにINSERTする方法。

やりたいこと

tableAとtableBをidでJOINして、tableA.columnAを重複なしに取り出しつつ、tableB.someColumnも一緒にnew_tableへINSERTしたい。

単純なDISTINCTだと重複が残るケース

INSERT INTO new_table (columnA, someColumn)
SELECT DISTINCT tableA.columnA, tableB.someColumn
FROM tableA
JOIN tableB ON tableA.id = tableB.id;

このクエリだとcolumnAだけを軸に重複排除されない。

DISTINCTは指定した全カラムの組み合わせで判定される

SELECT DISTINCTに複数カラムを並べた場合、重複判定は個々のカラムではなく列挙したカラムの組み合わせに対して行われる。

なのでcolumnAが同じ値でも、someColumnの値が違えば別の行として残る。「columnAだけ重複なしにしたい」という要求には合わない。

SELECT DISTINCT columnAのように1カラムだけ選ぶ場合は当然そのカラム単体で判定されるので問題ない。他のカラムも一緒に持ってきたい場合にズレが起きる。

DISTINCT ONを使う

PostgreSQLには標準SQLにはない拡張としてDISTINCT ONがある。指定した式が等しい行のグループのうち、最初の1行だけを残す構文。

INSERT INTO new_table (columnA, someColumn)
SELECT DISTINCT ON (tableA.columnA)
       tableA.columnA, tableB.someColumn
FROM tableA
JOIN tableB ON tableA.id = tableB.id
ORDER BY tableA.columnA, tableB.someColumn;

これでcolumnAを軸に重複排除される。someColumnは各columnAグループの中でどれか1つの値が採用される形になる。

ORDER BYは必須

DISTINCT ONを使うときはORDER BYをセットで書く必要がある。ORDER BYの先頭の式はDISTINCT ONに指定した式と一致していないといけない。

ORDER BYを省略すると「どの行が残るか」が不定になる。上の例だとsomeColumnの値が実行のたびに変わりうるということなので、必ずどの行を優先するかをORDER BYで明示する必要がある。

まとめ

  • 複数カラムをSELECT DISTINCTに並べると、それらの組み合わせで重複判定される。1カラムだけの重複排除にはならない。
  • 特定の1カラムを軸に重複排除しつつ他のカラムも持ってきたいならDISTINCT ON (対象カラム)を使う。
  • DISTINCT ONを使うときはORDER BY必須。順序を書かないとどの行が残るか保証されない。