分类：PostgreSql

2020
12-29

postgresql 实现更新序列的起始值

迁移老数据到新的数据库，需要把id迁移过来，这时序列值就需要修改为当前最大值加1了。使用这个语句就可以让s_seq的序列值从7开始了。altersequences_seqrestartwith7;补充：PostgreSQL设置主键自增、PostgreSQL重置主键自增从1开始，PostgreSQL清空表数据自增从1开始PostgreSQL设置主键自增、PostgreSQL重置主键自增从1开始自增字段从1开始清空表数据，自增从1开始自增字段PostgreSQL设置主键自增CREATETABLEstudent(id... 继续阅读 >
2020
12-29

postgresql修改自增序列操作

在mysql中，实现id自增的方式是依靠加一个auto_increment标志，而在pgsql中，id自增是通过序列SEQUENCE。创建表时设置自增序列为：CREATETABLE"config"("id"int4NOTNULLDEFAULTnextval('config_id_seq'::regclass),...};如果需要修改创建表之后的序列，如:----删除前先解除id对该序列的依赖ALTERTABLEtablenameALTERCOLUMNidSETDEFAULTnull;DROPSEQUENCEIFEXISTSsequence_name;----id_max即i... 继续阅读 >
2020
12-29

PostgreSQL之pgdump备份恢复操作

逻辑备份在恢复时，介于逻辑备份与故障时间点之间的数据难以恢复，故一般不采取逻辑备份方式进行数据库备份，但逻辑适用于跨平台跨版本的数据迁移；逻辑备份恢复主要以下三种：pg_dumppg_dumpallcopy本小节主要讲解pg_dumppg_dump备份只能备份单个数据库，不会导出角色和表空间相关的信息-Fc备份为二进制格式,压缩存储.并且可被pg_restore用于精细还原-Fp备份为文本,大库不推荐pg_dump恢复psqldbname-Uusername<bakfile或... 继续阅读 >
2020
12-29

PostgreSQL 分页查询时间的2种比较方法小结

数据库中存了3000W条数据，两种分页查询测试时间第一种SELECT*FROMtest_tableWHEREi_id>1000limit100;Time:0.016s第二种SELECT*FROMtest_tablelimit100OFFSET1000;Time:0.003s第一种SELECT*FROMtest_tableWHEREi_id>10000limit100;Time:0.004s第二种SELECT*FROMtest_tablelimit100OFFSET10000;Time:0.508s第一种：SELECT*FROMtest_tableWHEREi_id>100000limit100;Time:0.003s第二种：S... 继续阅读 >
2020
12-29

postgresql rank() over, dense_rank(), row_number()用法区别

如下学生表student，学生表中有姓名、分数、课程编号，需要按照课程对学生的成绩进行排序select*fromjinbo.student;id|name|score|course----+-------+-------+--------5|elic|70|14|dock|100|13|cark|80|12|bob|90|11|alice|60|110|jacky|80|29|iris|80|28|hill|60|17|grace|50|26|frank|70|26|test||2(11rows)1、rankove... 继续阅读 >
2020
12-29

在postgresql数据库中判断是否是数字和日期时间格式函数操作

在编写GreenPlum函数的过程中，遇到要判断字符串是否是数字和日期格式的情况，基于GreenPlum和postgresql的亲缘关系，找到了下面两个函数。--1.判断字符串是否是数字CREATEORREPLACEFUNCTIONisnumeric(txtStrVARCHAR)RETURNSBOOLEANAS$$BEGINRETURNtxtStr~'^([0-9]+[.]?[0-9]*|[.][0-9]+)$';END;$$LANGUAGE'plpgsql';--2.判断传入的字符串是否是日期格式CREATEORREPLACEFUNCTIONisDate(dateStrVARCHAR)RETU... 继续阅读 >
2020
12-29

postgresql 实现sql多行语句合并一行

多行语句合并一行三个表关联查询的结果集为：SELECTusers.name,users.age,users.birthday,roles.nameFROMusers,users_roles,rolesWHEREusers_roles.userid=users.idandusers_roles.roleid=roles.idnameagebirthdayrole张三231993-1-1role1张三231993-1-1role2张三231993-1-1role3以下语句执行后SELECTusers.name,users.age,users.birthday,array_to_string(ARRAY(SELECTunnest(array_agg(roles.name)))... 继续阅读 >
2020
12-29

Postgresql自定义函数详解

PostgreSQL函数也称为PostgreSQL存储过程。PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。语法：CREATE[ORREPLACE]FUNCTIONfunction_name(arguments)RETURNSreturn_datatypeAS$variable_name$DECLAREdeclaration;[...]BEGIN<function_body>[...]RETURN{variable_name|value}END;LANGUAGEplpgsql;参数说明fun... 继续阅读 >
2020
12-29

PostgreSQL删除更新优化操作

1.先说删除吧，因为刚搞了。删除缓慢的原因：主要是约束的问题。（数据库在有约束的时候，进行操作，会根据约束对相关表进行验证，可想而知，20W的数据验证要耗费多久的时间）。其次就是sql的编写。（sql如果查询中包含子查询等的可以优化的where会影响匹配的速度<查询的话就不多逼逼了>）。索引的问题请看下面的补充部分具体解决方法：ALTERTABLEtableNameDISABLETRIGGERALL;delete目标语句ALTERTABLEtableNameEN... 继续阅读 >
2020
12-29

postgresql通过索引优化查询速度操作

postgresql通过索引优化查询速度操作 当数据量比较大的时候，提升查询效率就是需要去考虑的事情了。一个百万级别的表格，如果不做任何优化的话，即使是最简单的查询语句执行起来也是慢的让人难以接受；当然“优化”本身是一个比较复杂的工程，从设计表、字段到查询语句的写法都有很多讲究，这里只考虑索引的方式，且是最普通的索引；下面的操作中对应数据库表w008_execrise_info（8000数据量），w008_wf02_info（4000数据量）1任务表数据SELECTw.*FROMw008_wf02_in... 继续阅读 >
2020
12-29

postgreSQL 非count方法算记录数操作

一般方法selectcount(1)fromtable_name;全量扫描一遍表，记录越多，查询速度越慢新法PostgreSQL还真提供了一个这样的途径，那就是系统表pg_class，这个系统表里头，存储着每个表的统计信息，其中reltuples就是对应的表的统计行,统计行的数据是pg有个独立进程，定期扫描不同的表，收集这些表的统计信息，保存在系统表里头。方法如下：selectreltuples::intastotalfrompg_classwhererelname='table_name'andre... 继续阅读 >
2020
12-29

postgresql的now()与Oracle的sysdate区别说明

postgresql的now()为当前事务开始时间，而Oracle的sysdate是当前时间。区别在于事务。postgresql中的now()：postgres=#begin;BEGINpostgres=#selectnow();now-------------------------------2017-03-3114:28:32.403869+08(1row)postgres=#selectnow();now-------------------------------2017-03-3114:28:32.403869+08(1row)postgres=#selectnow();now-------------------------------2017-03-3114... 继续阅读 >
2020
12-29

Postgresql 通过出生日期获取年龄的操作

Postgresql 通过出生日期获取年龄的操作 三个基础的时间表示函数CURRENT_DATE/CURRENT_TIME/NOW()SELECTCURRENT_DATE;返回当前日期以年-月-日（yyyy-MM-dd）的形式：2019-01-10SELECTCURRENT_TIME;返回当日时间以时:分:秒+时区（HH:mm:ss）的形式：17:49:11.585308+08SELECTNOW();返回当前时间以年-月-日时:分:秒（yyyy-MM-ddHH:mm:ss）的形式：2019-01-1017:50:45.354983+0然后几个计算公式第一种是直接对日期进行操作，SELECT(CURRENT_DATE-'1993-01-01... 继续阅读 >
2020
12-29

postgresql 获取两个时间类型小时差值案例

我就废话不多说了，看代码吧~selectextract(dayfromt)*24+extract(hourfromt)from(select(timestamp'2013-12-1412:00:00'-timestamp'2013-12-114:00:00')ast)asa;例如a表中有start，end俩字段selectextract(dayfrom(end-start))*24+extract(hourfrom(end-start))froma补充：PostgreSQL得出两个timestamp的秒数差值sql语句SELECTEXTRACT(EPOCHFROM(TIMESTAMP'2017-12-1612:21:13'-TIMESTAMP... 继续阅读 >
2020
12-29

postgresql中时间转换和加减操作

昨天遇到一个floor向下取整的问题，现在将它记录下来。首先floor是需要一个int或者dp。那么我们日期加减转换，timestamp转data，是需要先将其转成char，再由char转date,再相减，得出一个符合floor函数要求的值。代码如下：FLOOR((to_date(to_char(current_timestamp,'yyyy-MM-ddhh:mi:ss'),'yyyy-mm-dd')-to_date(to_char(ca.birthday,'yyyy-MM-ddhh:mi:ss'),'yyyy-mm-dd'))/365)asage同样的，在java里面要想得到times... 继续阅读 >