Postgresql去重函数distinct的用法说明

在项目中我们常会对数据进行去重处理，有时候会用in或者EXISTS函数。或者通过group by也是可以实现查重

不过Postgresql还有自带去重函数：distinct

下面是distinct 的实例：

1、创建表：user

CREATE TABLE `user` (
 `name` varchar(30) DEFAULT NULL,
 `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('张三', 20);
INSERT INTO `user` VALUES ('李四', 22);
INSERT INTO `user` VALUES ('李四', 20);
INSERT INTO `user` VALUES ('张三', 22);
INSERT INTO `user` VALUES ('张三', 20);

查询结果：

SELECT * FROM user
张三 20
李四 22
李四 20
张三 22
张三 20

2、根据 name 查询去重后的数据：

SELECT distinct name FROM user
张三
李四

3、根据name 和 age 查询去重后的数据：

SELECT distinct name,age FROM user
张三 20
李四 22
李四 20
张三 22

4、根据name,age查询重复数据数：

SELECT distinct name,age,count(*) 数据条数 FROM user GROUP BY name,age
张三 20 2
张三 22 1
李四 20 1
李四 22 1

二、查出重复数据后，我们需要删除重复数据

删除重复数据一般几种方式，一般采用 临时表 或者根据 某个字段，例如id等，通过max或者min函数去重。

补充：基于postgresql ctid实现数据的差异同步

项目背景：

最近在做异构数据同步方面（非实时）的工作，从oracle,gbase,postgresql向mysql数据库中同步，对于没有自增字段（自增ID或时间字段）的业务表，做差异同步是一件非常麻烦的事情，主要体现在记录的新增、更新与删除上

备注：源库只提供一个只读权限的用户

ctid在pg中的作用

ctid是用来指向自身或新元组的元组标识符，怎么理解呢？下面能过几个实验来测试一下

satdb=# create table test_ctid(id int,name varchar(100));
satdb=# insert into test_ctid values(1,‘a'),(1,‘a');
satdb=# insert into test_ctid values(2,‘a'),(3,‘a');

查看记录的ctid值

satdb=# select id,name,ctid from test_ctid;
id | name | ctid
----±-----±------
1 | a | (0,1)
1 | a | (0,2)
2 | a | (0,3)
3 | a | (0,4)
(4 rows)

对id为2的记录进行更新

satdb=# update test_ctid set name=‘b' where id=2;
UPDATE 1

这里可以看到id=2的记录指向了新的元组标识符 (0,5)

satdb=# select id,name,ctid from test_ctid;
id | name | ctid
----±-----±------
1 | a | (0,1)
1 | a | (0,2)
3 | a | (0,4)
2 | b | (0,5)
(4 rows)
satdb=# select * from test_ctid where ctid='(0,1)';
id | name
----±-----
1 | a
(1 row)

删除 id=3的记录后，对应的ctid(0,4)不存在了

satdb=# delete from test_ctid where id=3;
DELETE 1
satdb=# select *,ctid from test_ctid;
id | name | ctid
----±-----±------
1 | a | (0,1)
1 | a | (0,2)
2 | b | (0,5)
(3 rows)

再插入一条记录时，看看会不会使用(0,4)这个标识符

satdb=# insert into test_ctid values(3,‘d');
INSERT 0 1
satdb=# select *,ctid from test_ctid;
id | name | ctid
----±-----±------
1 | a | (0,1)
1 | a | (0,2)
2 | b | (0,5)
3 | d | (0,6)

这里新插入的记录不会使用(0,4)，而是直接分配新的标识符(0,6)

总结：

1、ctid的作用与oracle rowid类似，可以唯一标识一条记录

2、记录的更新后，后生产新的ctid

3、记录删除后，新插入的记录不会使用已经删除记录的ctid

4、基于ctid可以实现记录的去重操作

5、基于ctid可以实现差异增量同步(新增、删除、更新)

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

赞 (0)

相关推荐

  • postgresql初始化之initdb的使用详解

    initdb 官网 initdb创建了一个新的PostgreSQL数据库集群.数据库集群是由单个服务器实例管理的数据库集合. 创建数据库集群包括数据库所在的目录.生成共享目录表(属于整个集群而不是任何特定数据库的表)以及创建template1和postgres数据库.稍后创建新数据库时,将复制template1数据库中的所有内容(因此,template1中安装的任何内容都会自动复制到以后创建的每个数据库中.),postgres数据库是供用户.实例程序和第三方应用程序使用的默认数据库. 尽管ini

  • 开源数据库postgreSQL13在麒麟v10sp1源码安装过程详解

    一.中标麒麟v10sp1在飞腾2000+系统安装略 二.系统依赖包安装 [root@ft2000db opt]# yum install bzip* [root@ft2000db opt]# nkvers ############## Kylin Linux Version ################# Release: Kylin Linux Advanced Server release V10 (Tercel) Kernel: 4.19.90-17.ky10.aarch64 Buil

  • postgresql 如何查看pg_wal目录下xlog文件总大小

    当然如果你登录服务器所在主机,直接在$PGDAT/pg_wal下执行: du -h --max-depth=1 ./ 可以得到. #du -h --max-depth=1 ./ 4.0K ./archive_status 193M ./ 如果通过客户端怎么做呢? 答案:pg_ls_waldir()函数.pg_ls_waldir()是pg 10.0引入的函数,可以输出数据库WAL目录的所有文件. postgres=# select sum(size) from pg_ls_waldir(); su

  • postgresql运维之远程迁移操作

    postgresql运维之远程迁移操作

    背景:高可用架构版本. 主备分别部署在机器A和B上,现在要将其分别迁移到机器C和D上. 思路: 1.首先根据源实例的备份(云盘上可用snapshot),创建一个mirror实例,mirror包含两个节点,分别部署在C和D上. 2.在源实例主节点hba.conf中增加mirror主节点的ip的设置,允许源实例主节点接受来自mirror主节点的连接. 3.mirror实例主节点,创建recovery.conf文件,设置primary_conninfo指向源主节点.启动mirror主节点,建立源实例主

  • 修改postgresql存储目录的操作方式

    修改postgresql存储目录: sudo rsync -av /var/opt/gitlab/postgresql /data/gitlab/ 修改 /etc/gitlab/gitlab.rb postgresql['data_dir'] = "/data/gitlab/postgresql/data" postgresql['dir'] = "/data/gitlab/postgresql" postgresql['home'] = "/data/g

  • PostgreSQL 实现distinct关键字给单独的几列去重

    PostgreSQL 实现distinct关键字给单独的几列去重

    PostgreSQL去重问题一直困扰着我,distinct和group by远不如MySQL用起来随便,但是如果掌握了规律,还是和MySQL差不多的 主要介绍的是distinct关键字 select distinct id,name,sex,age from student 假如有一张student表,字段如上图,我查询student表中所有信息用distinct去重(上面的SQL语句),pgsql就会根据所有的字段通过算法取得重复行的第一行,但是很明显,ID这个字段我在设计的时候不会让它重复,

  • postgresql之使用lsn 获取 wal文件名的实例

    10.0及以后版本: pg_walfile_name() 用法: postgres=# select pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()), pg_walfile_name_offset(pg_current_wal_lsn()); pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset --------------------+---------

  • postgresql 12版本搭建及主备部署操作

    postgresql 12版本主备部署 环境搭建 centos 7+ postgresql 12.0 # 网络检查 ping -c2 baidu.com #关闭防火墙,selinux systemctl stop firewalld && sudo systemctl disable firewalld sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config setenforce 0 ### 配置阿里云yum源

  • Postgresql去重函数distinct的用法说明

    在项目中我们常会对数据进行去重处理,有时候会用in或者EXISTS函数.或者通过group by也是可以实现查重 不过Postgresql还有自带去重函数:distinct 下面是distinct 的实例: 1.创建表:user CREATE TABLE `user` ( `name` varchar(30) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `us

  • PostgreSQL聚合函数的分组排序使用示例

    PostgreSQL聚合函数的分组排序使用示例

    聚合函数 用于汇总的函数. COUNT COUNT,计算表中的行数(记录数). 计算全部数据的行数: SELECT COUNT(*) FROM Product; NULL之外的数据行数: SELECT COUNT(purchase_price) FROM Product; 结果如下图. 对于一个含NULL的表: 将列名作为参数,得到NULL之外的数据行数:将星号作为参数,得到所有数据的行数(包含NULL). SUM.AVG SUM.AVG函数只能对数值类型的列使用. SUM,求表中的数值列的数据

  • SQL函数Group_concat的用法及说明

    目录 SQL函数Group_concat的用法 完整语法如下 Group_concat函数长度问题 解决的方式有三个 总结 SQL函数Group_concat的用法 完整语法如下 group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) SELECT * FROM testgroup 表结构与数据如上 现在的需求就是每个id为一行 在前台每行显示该id所有分数 group_concat 上场!!! SEL

  • SQL Server中row_number函数的常见用法示例详解

    一.SQL Server Row_number函数简介 ROW_NUMBER()是一个Window函数,它为结果集的分区中的每一行分配一个连续的整数. 行号以每个分区中第一行的行号开头. 以下是ROW_NUMBER()函数的语法实例: select *,row_number() over(partition by column1 order by column2) as n from tablename 在上面语法中: PARTITION BY子句将结果集划分为分区. ROW_NUMBER()函

  • PHP回调函数概念与用法实例分析

    本文实例讲述了PHP回调函数概念与用法.分享给大家供大家参考,具体如下: 一.回调函数的概念 先看一下C语言里的回调函数:回调函数就是一个通过函数指针调用的函数.如果你把函数的指针(地址)作为参数传递给另一个函数,当这个指针被用来调用其所指向的函数时,我们就说这是回调函数.回调函数不是由该函数的实现方直接调用,而是在特定的事件或条件发生时由另外的一方调用的,用于对该事件或条件进行响应. 其他语言里的回调函数的概念与之相似,只不过各种语言里回调函数的实现机制不一样,通俗的来说,回调函数是一个我们定

  • 如何理解Vue的render函数的具体用法

    本文介绍了如何理解Vue的render函数的具体用法,分享给大家,具体如下: 第一个参数(必须) - {String | Object | Function} <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>render</title> <script src="https://cdn.b

  • JS中的Replace()传入函数时的用法详解

    replace方法的语法是:stringObj.replace(rgExp, replaceText) 其中stringObj是字符串(string),reExp可以是正则表达式对象(RegExp)也可以是字符串(string),replaceText是替代查找到的字符串.. 废话不多说了,直接给大家贴代码了,具体代码如下所示: <script> var str = "a1ba2b"; var reg = /a.b/g; str = str.replace(reg,func

  • python函数装饰器用法实例详解

    本文实例讲述了python函数装饰器用法.分享给大家供大家参考.具体如下: 装饰器经常被用于有切面需求的场景,较为经典的有插入日志.性能测试.事务处理等.装饰器是解决这类问题的绝佳设计, 有了装饰器,我们就可以抽离出大量函数中与函数功能本身无关的雷同代码并继续重用.概括的讲,装饰器的作用就是为已经存在的对象添加额外的功能. #! coding=utf-8 import time def timeit(func): def wrapper(a): start = time.clock() func

  • Sort()函数的多种用法

    sort() 方法用于对数组的元素进行排序.包含于头文件algorithm 语法 arrayObject.sort(sortby) 参数 描述 sortby 可选.规定排序顺序.必须是函数. 返回值 对数组的引用.请注意,数组在原数组上进行排序,不生成副本. 一.默认情况 在默认情况下, sort() 方法按升序排列数组项.为了实现排序, sort() 方法会调用每个数组项的 toString() 转型方法,然后比较得到的字符串,已确定如何排序.如下: var values = ["orange

  • js中判断变量类型函数typeof的用法总结

    1.作用: typeof 运算符返回一个用来表示表达式的数据类型的字符串. 可能的字符串有:"number"."string"."boolean"."object"."function" 和 "undefined". 2.常用返回值说明 表达式 返回值 typeof undefined 'undefined' typeof null 'object' typeof true 'boole

随机推荐