Detailed explanation of the special limitations of PostgreSQL on the use of constants on GROUP BY clause

1. Problem description

Recently, when a statistical program was ported from Oracle to PostgreSQL (version 9.4), it reported errors one after another:

Error message 1: postgresql group by position 0 is not in select list.

Error message 2: non-integer constant in GROUP BY.

The SQL that produces the error is similar to:

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';

Among them, the detail_tab table saves the original detailed records, while the sum_tab saves the statistical record information.

2. Cause analysis

After testing, it was found that the error was because PostgreSQL had special restrictions on the use of constants on the GROUP BY clause. The testing process is too cumbersome, so I won’t write demos one by one here, and I will give a conclusion directly:

1 String and floating-point constants cannot be used in the GROUP BY clause, otherwise an error message 2 will be reported. like:

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

2 The GROUP BY clause cannot use 0 and negative integers, otherwise an error message 1 will be reported. like:

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

So, what type of constants can be used in the GROUP BY clause? After testing, among commonly used types, positive integers and date constants are both OK.

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

For the sql in the first section, since 0 and 'null' have special meanings, how should we deal with it?

In fact, you can not use any constants in the GROUP BY clause, just list the aggregated fields, that is, change the SQL in the first section to:

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

3. The situation of MySQL

Considering that the statistical procedures may also be ported to MySQL (version), similar tests were subsequently performed, and the conclusion was:

1 Supports GROUP BY clause without any constants;

2 Supports GROUP BY clauses with non-0 integers, floating-point numbers (including 0.0), strings, and date-type constants.

That is, among common types, the GROUP BY clause of MySQL 8 supports all types except integer 0 (non-floating point number 0.0). Otherwise, an error will be reported:

ERROR 1054 (42S22): Unknown column '0' in 'group statement'

By the way, Oracle also supports integer 0.

4. Conclusion

1. The GROUP BY clause of PostgreSQL only supports positive integers and date-type constants;

2. MySQL supports all regular type constants except for 0 integers, while Oracle seems to support them all;

3. If there is a need to be portable on the Colombian database platform, try not to use constants in the GROUP BY clause.

Supplement: GROUP BY Issue for PostgreSQL

Regarding PostgreSQL database group query, there is still a difference between it and mysql. Troubled for a long time

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

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.