PostgreSQL对GROUP BY子句使用常量的特殊限制详解

一、问题描述

最近，一个统计程序从Oracle移植到PostgreSQL（版本9.4）时，接连报告错误：

错误信息1： postgresql group by position 0 is not in select list.

错误信息2： non-integer constant in 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保存统计后的记录信息。

二、原因分析

经过测试，发现错误是因为PostgreSQL对GROUP BY子句使对使用常量有着特殊限制。测试过程过于繁琐，这里不再一一写demo了，直接给出结论：

1 GROUP BY子句中不能使用字符串型、浮点数型常量， 否则会报告错误信息2。如：

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子句中可以不使用任何常量，只列出聚集字段即可，即将第一节中的sql改为：

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

三、MySQL的情况

考虑到将来统计程序也可能移植到MySQL（版本8.x），随后进行了类似测试，结论为：

1 支持不带任何常量的GROUP BY子句；

2 支持带非0整数、浮点数（包括0.0）、字符串、日期型常量的GROUP BY子句。

也就是说，在常见类型中，MySQL 8的GROUP BY子句支持除整数0（非浮点数0.0）以外的所有类型。否则，会报错：

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

顺便说一句，Oracle对整数0也支持。

四、结论

1、PostgreSQL的GROUP BY子句只支持正整数、日期型的常量；

2、MySQL支持除非0整数以外的所有常规类型常量，而Oracle似乎全部支持；

3、如果有在各各数据库平台可移植的需求，尽量不要在GROUP BY子句中使用常量。

补充：PostgreSQL的GROUP BY问题

关于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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • PostgreSQL中的VACUUM命令用法说明

    每当PostgreSQL数据库中的表中的行被更新或删除时,死亡行会被遗留下来.VACUUM则会把它们除去来使空间能被重新利用.如果一个表没有被清空,它会变得臃肿,浪费磁盘空间而且会降低顺序表扫描的速度,而且在较小范围内也会降低索引扫描的速度. VACUUM命令只可以移除这些不再被需要的行版本(也被称为元组).如果被删除事务的事务ID(存储在xmax系统列中)比仍然活跃在PostgreSQL数据库(或者共享表的整个集群)中最老的事务(xmin界限)更老,那么这个元组将不再被需要. 注意以下三种情况

  • PostgreSQL备份工具 pgBackRest使用详解

    前言 pgBackRest是一款开源的备份还原工具,目标旨在为备份和还原提供可靠易用的备份. 特性 并行备份和还原 备份操作期间压缩通常是其瓶颈所在.pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题. 本地远程操作 自定义协议允许 pgBackRest以最小化配置通过SSH在本地或者远程执行备份.还原和归档.并且该程序也通过协议层提供了PostgreSQL查询接口,以便于必须要再远程访问PostgreSQL,从而保证了其安全性能. 全量,增量和差异备份 支持全量,增量和差异备份.

  • PostgreSQL 如何查找需要收集的vacuum 表信息

    前言 通常,在PostgreSQL中,由于经常需要对表进行 UPDATE 和 DELETE,因此表会产生碎片空间. 在 PostgreSQL中,使用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未使用,以便以后重用这些空间,但是不能立即将占用的空间返还给操作系统,因此需要使用 VACUUM FULL,才可以释放空间,并立即将空间返还给操作系统. 实现脚本 记录收集表创建 CREATE TABLE IF NOT EXISTS tab_vacuum_record (sqltext

  • postgresql数据添加两个字段联合唯一的操作

    postgresql数据添加两个字段联合唯一的操作

    我就废话不多说了,大家还是直接看代码吧~ alter table tb_safety_commitment add constraint uk_mac_vendor unique (company_code,promise_date); 补充:一行数据中的 多字段值根据连接符拼接 concat_ws(':',a,b) 几行数据中的 同一 单字段值根据连接符拼接 string_agg(c,' \r\n ') 如果要将多个字段的值拼接成一个: string_agg(concat_ws(':',a,b

  • postgreSQL使用pgAdmin备份服务器数据的方法

    postgreSQL使用pgAdmin备份服务器数据的方法

    使用postgresql的时候,有时候需要备份数据,但是数据库又比较多,比如我们的数据库如下: 这就很烦了,需要一种一键备份还原的方法来备份还原. 1.备份 备份可以使用pgadmin自带的备份服务器.右键服务器连接,点击备份服务器,再选择创建的文件与数据库编码(一般为UTF8),即可,感觉比命令行的方式好用多了. 当然也可以使用命令行的方式,以下来自百度,试过没有成功,觉得太麻烦了就放弃了,供参考: SQL备份 pg_dumpall.exe -h localhost -p 5432 -U po

  • postgresql数据合并,多条数据合并成1条的操作

    postgresql数据合并,多条数据合并成1条的操作

    对于主表中一条记录,对应明细表中的96条数据,每一条数据相隔15分钟,明细中没96条数据对应主表中的一个日期trade_date,并且每条明细中有一个字段start_time, 即明细中每96条数据中第一条数据中start_time为00:00, 第二条为00:15,第三条为00:30,依次类推,直到23:45 ,现在要将明细表中的96条数据合并成24条,即第一条数据中start_time为00:00,第二条为01:00,第三条为02:00 sql:select max(de.bid_num)

  • Postgresql 查看SQL语句执行效率的操作

    Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看 SQL 语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句. Explain语法: explain select - from - [where ...] 例如: explain select * from dual; 这里有一个简单的例子,如下: EXPLAIN SELECT * FROM tenk1; QUERY PLAN ---------

  • PostgreSQL对GROUP BY子句使用常量的特殊限制详解

    一.问题描述 最近,一个统计程序从Oracle移植到PostgreSQL(版本9.4)时,接连报告错误: 错误信息1: postgresql group by position 0 is not in select list. 错误信息2: non-integer constant in GROUP BY. 产生错误的sql类似于: insert into sum_tab (IntField1, IntField2, StrField1, StrField2, cnt) select IntFi

  • php的常量和变量实例详解

    php的常量和变量实例详解 [PHP在命令行下接收参数] 如果在命令行调试php,传入的参数通过$argv获取,注意其中包含了文件名这一个元素,数组中元素的个数通过$argc获取. [可变变量] 指的是变量的名称可变,变量的标识符可以由另一个变量的值来代替. 例如:第二个语句是为argv1这个变量赋值. <?php $varName = 'argv1'; $$varName = 'value1'; var_dump($argv1); ?> [常量] 使用define定义,不可删除和修改,调用时

  • Java 常量池的实例详解

    Java 常量池的实例详解 Java的常量池中包含了类.接口.方法.字符串等一系列常量值.常量池在编译期间就已经确定,并保存在*.class文件中 一.对于相同的常量值,常量池中只保存一份拷贝. 而且,当一个字符串由多个字符串常量链接而成时,多个字符串被组成一个字符串常量. 例如: package lxg; public class main { public static void main(String[] args) { String name = "lengxuegang";

  • python中的常量和变量代码详解

    局部和全局变量: # name='lhf' # def change_name(): # # global name # name='帅了一比' # print('change_name',name) # # # change_name() # print(name) # name='lhf' # # def change_name(): # name1='帅了一比' # name2='帅了一比' # name3='帅了一比' # print('change_name',name) # # ch

  • SQL Server2008 Order by在union子句不可直接使用的原因详解

    按照要求,每个取top 20,既然是随机的取,那么就SQL Server Order by newid()就是了,然后把所有数据union起来就得了.所以我立即给出了答案: selecttop 20 *fromxxxwheretype=1orderbynewid() union selecttop 20 *fromxxxwheretype=0orderbynewid() 但是在sql 查询分析器种不对,语法有错,我乍一看,好像没有问题吧: selecttop 20 *fromxxxwherety

  • python学习字符串驻留与常量折叠隐藏特性详解

    python学习字符串驻留与常量折叠隐藏特性详解

    下面是Python字符串的一些微妙的特性,绝对会让你大吃一惊. 案例一: 案例二: 案例三: 很好理解, 对吧? 说明: 这些行为是由于 Cpython 在编译优化时, 某些情况下会尝试使用已经存在的不可变对象而不是每次都创建一个新对象. (这种行为被称作字符串的驻留[string interning]) 发生驻留之后, 许多变量可能指向内存中的相同字符串对象. (从而节省内存) 在上面的代码中, 字符串是隐式驻留的. 何时发生隐式驻留则取决于具体的实现. 这里有一些方法可以用来猜测字符串是否会

  • PostgreSQL 查看表的主外键等约束关系详解

    我就废话不多说了,大家还是直接看代码吧~ SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name, tc.is_deferrable,tc.initially_deferred FROM information_schema.table_constraints AS tc JOIN

  • Swift 常量与变量实例详解

    Swift 常量与变量实例详解

    1.声明 swift用let对常量的声明,用var对变量进行声明 常量声明之后不可更改,变量声明之后可以对值进行更改 对常量变量的声明代码如下: let maxNum = 1000 var index = 2 注意:常量不能更改 注意:声明的时候关键字与变量名之间要空一个空格 2.连续声明 连续声明每个变量之间用","隔开 var x = 1, y = 2, z = 3 3.变量类型 当变量赋值时,swift自动识别变量类型,种几种机制叫做"type inference&qu

  • Vue中定义全局变量与常量的各种方式详解

    前言 本文主要跟大家介绍了关于Vue定义全局变量与常量的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍: 我想要定义一个变量, 在项目的任何地方都可以访问到, 不需要每一次使用的时候, 都引入. 尝试1: 创建 global.js 并且在其中定义 let a = 10; 在入口文件中引入 global.js import './global.js' 在项目中使用: a // 报错 发现报错了, a 并没有定义. 为什么? 这个涉及到模块作用域: 1 每一个 js 都相当于

  • Java关键字、标识符、常量、变量语法详解

    Java关键字、标识符、常量、变量语法详解

    一.关键字 关键字:被Java语言赋予特定含义的单词.组成关键字的字母全部小写.注:goto和const作为保留字存在,目前并不使用.main并不是关键字. 二.标识符 标识符:就是给类,接口,方法,变量等起名字时使用的字符序列. 组成规则:英文大小写字母.数字字符.$和_ 注意事项:不能以数字开头.不能是Java中的关键字.区分大小写 我们通常会给下面的这些东西起一个标识符: 包.类或者接口.方法和变量.常量 三.注释 单行注释的格式: //注释文字 多行注释的格式: /* 注释文字 */ 文

随机推荐