GROUP BY句での定数使用に関するPostgreSQLの特別な制限について説明します。

I. 問題の説明

最近、OracleからPostgreSQL（バージョン9.4）に移植した統計アプリケーションで、次々とエラーが報告されました。

エラーメッセージ 1: postgresql group by position 0 is not in select list.

エラーメッセージ 2: GROUP BY に非整数の定数があります。

エラーが発生したsqlは、以下のようなものです。

insert into sum_tab (IntField1, IntField2, StrField1, StrField2, cnt)
select IntField, 0, StrField, 'null', count(*) from detail_tab
where ...
group by IntField, 0, StrField, 'null';

ここで、detail_tabテーブルには元の詳細レコードが、sum_tabには統計レコードの情報が格納される。

II. 原因分析

テストの結果、このエラーはPostgreSQLがGROUP BY句での定数の使用に特別な制限を設けているためであることがわかりました。テスト作業はあまりにも面倒なので、ここでは一つ一つのデモを書かず、結論を直接お伝えすることにします。

1 GROUP BY 節で文字列定数や浮動小数点数定数を使用することはできません。

select IntField, 'aaa', count(*) from tab group by IntField, 'aaa'; 
select IntField, 0.5, count(*) from tab group by IntField, 0.5;

2 GROUP BY 節では、0 や負の整数を使用してはいけません。そうでない場合は、エラーメッセージ 1 が報告されます。

select IntField, 0, count(*) from tab group by IntField, 0;
select IntField, -1, count(*) from tab group by IntField, -1;

では、GROUP BY句で使用できる定数はどのようなものでしょうか。検証の結果、一般的な型の中では、正の整数型と日付型の定数が問題ないようです。

select IntField, 1, count(*) from tab group by IntField, 1;
select IntField, now(), count(*) from tab group by IntField, now();

最初のセクションのSQLは、0と'null'が特別な意味を持つため、どのように処理すればよいですか？

GROUP BY句の定数を削除して、集約されたフィールドだけをリストアップすることができます。

insert into sum_tab (IntField1, IntField2, StrField1, StrField2, cnt)
select IntField, 0, StrField, 'null', count(*) from detail_tab
where ...
group by IntField, StrField;

III. MySQL の場合

将来的に統計プログラムをMySQLにも移植する可能性を考慮し（バージョン8.x）、その後同様のテストを実施した結果、以下のような結論となりました。

1 定数なしのGROUP BY句をサポート。

2 ゼロ以外の整数、浮動小数点数（0.0を含む）、文字列、日付型定数によるGROUP BY句をサポートします。

つまり、一般的な型のうち、MySQL 8 の GROUP BY 句は、整数 0（非浮動小数点 0.0） を除くすべての型をサポートしています。それ以外の場合は、次のようにエラーが報告されます。

エラー 1054 (42s22)。group文」の不明な列「0」。

ちなみに、Oracleは整数の0もサポートしています。

IV. まとめ

1. PostgreSQL の GROUP BY 句は、正の整数の日付型定数のみをサポートしています。

2. MySQLは0でない整数を除く全ての通常型定数をサポートしていますが、Oracleは全ての定数をサポートしているようです。

3. データベースプラットフォーム間での移植性の必要性がある場合、GROUP BY句で定数を使用しないようにしてください。

追記 PostgreSQLのGROUP BY問題

PostgreSQLデータベースでクエリをグループ化する場合、PostgreSQLとmysqlの間にはまだ違いがあります。これにはしばらく悩まされました。

SELECT
 prjnumber,
 zjhm,
-- to_char ( to_timestamp ( kqsj / 1000 ), 'yyyy-MM-dd HH24:MI:SS' ) kqsj,

 kqflag,
 workername,
 max(kqsj)
 
-- workertype,
-- tpcodename,
-- isactive 
FROM
 GB_CLOCKINGIN 
WHERE
 kqsj BETWEEN 1590940800000 AND 1593532799000 
 AND prjnumber = '3205842019121101A01000' 

GROUP BY 
 zjhm,
 kqflag,
 prjnumber,
 workername

上記は私の個人的な経験ですが、参考にしていただき、BinaryDevelopをより支持していただければと思います。もし、間違いや不完全な考察があれば、遠慮なくアドバイスしてください。