一名数据库爱好者的专栏

导出的文件存放在执行psql的客户端。

【代码】postgresql 数据库中批量创建索引。

【代码】plpgsql 的 for 循环使用。

【代码】postgresql 批量创建分区表。

触发器

触发器

https://blog.youkuaiyun.com/qq_33158376/article/details/86609797

os: centos 7.6.1810db: postgresql 10版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # # yum list installed|grep -i postgrepostgresql10.x86_64 10.18-1PGDG.rhel7 @pgdg10 postgresql10-contrib.x86_64

Well, In postgres, it seems there’s no such function equivalent to LAST_DAY() available in oracle.If you need to, you can have your own in the following ways as aSelect QuerySELECT (date_trunc('MONTH', now()) + INTERVAL '1 MONTH - 1 day')::date;plsql

os: centos 7.6.1810db: postgresql 10版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # yum list installed |grep -i postgresqlpostgresql10.x86_64 10.16-1PGDG.rhel7 @pgdg10 postgresql10-contrib.x86_64

os: centos 7.6.1810db: postgresql 10版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # yum list installed |grep -i postgresqlpostgresql10.x86_64 10.16-1PGDG.rhel7 @pgdg10 postgresql10-contrib.x86_64

#!/bin/bashfor((i=1;i<=$1;i++));do(/bin/psql <<EOF begin; DO \$\$ DECLARE lv_var varchar; lv_rec record; BEGIN for lv_rec in ( select trunc(random()*1000000::int4):

os: centos 7.4db: postgresql 11.10版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # yum list installed |grep -i postgresqlRepodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fastpostgresql11.x86_64 1

SQL执行顺序：(8)SELECT (9)DISTINCT <select_list>(1)FROM <left_table>(3)<join_type> JOIN <right_table>(2)ON <join_condition>(4)WHERE <where_condition>(5)GROUP <group_by_list>(6)WITH {CUBE|ROLLUP}(7)HAVING <hav

os: centos 7.8.2003db: postgresql 13.0inpostgres=# explain ( analyze on ,verbose on ,timing on) with tmp_t0 as ( select 1 as id union all select 2 as id union all select 3 as id union all select 4 as id),tmp_t1 as ( select 2 as id union all

os: centos 7.4.1708db: postgresql 10.11json 数据类型存储输入文本的精准拷贝，处理函数必须在每次执行时必须重新解析该数据。jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支持索引。版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list i

array_to_json()postgres=# select array_to_json('{{"a",1},{"b",2}}'::varchar[]); array_to_json ----------------------- [["a","1"],["b","2"]](1 row)row_to_json()postgres=# select row_to_json(row('a','1')); row_to_json -------------

表转为jsonselect json_object(array_agg(t0.id), array_agg(t0.name)) from ( select id::varchar id,md5(id::varchar) as name from generate_series(1,3) as id ) t0 ; json_object

postgresql 添加新列时，不可以指定新列的位置，是添加在原有列的后面。参考 http://wiki.postgresql.org/wiki/Alter_column_position

触发器函数CREATE OR REPLACE FUNCTION public.f_trg_tmp_wal_compress()RETURNS triggerLANGUAGE plpgsqlAS $function$ DECLARE VN_COUNT INTEGER;BEGIN --语句级触发的不处理 IF TG_LEVEL = 'STATEMENT' THEN RETURN NULL; END IF; --BEFORE触发的不处理

初步sql，思路是先获取两个时间差的秒数，再相除获取时间和分钟缺点是没有考虑负时间select case when char_length(trunc(a.between_sec/a.hour_sec)::varchar ) <=2 then lpad(trunc(a.between_sec/a.hour_sec)::varchar,2,'0') else trunc(a.between_sec/a.hour_sec)::varchar

os: centos 7.4.1708db: postgresql 10.11版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i postgresqlpostgresql11.x86_64 11.8-1PGDG.rhel7 @pgdg11postgresql11-contrib.x

postgresql 9.6 查询 pg_stat_activity、pg_stat_statements 时部分列显示postgres=> select pid,client_addr,query from pg_stat_activity; pid | client_addr | query ------+-------------+---------------------------------

like匹配ilike不区分大小写匹配~~等价于 like~~*等价于 ilike~匹配正则表达式，大小写相关~*匹配正则表达式，大小写无关通配符%百分号用于匹配字符串序列，可匹配任意组合_下划线用于匹配任何单一字符如果想要做前缀匹配或后缀匹配，可以用下面的方法1、前缀模糊查询。selec...

os: centos 7.4db: postgresql 10.11postgresql 默认的 isolation level 为 read committed，可以调整隔离级别。版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # su - postgresLast login: Wed J...

os: centos 7.4db: postgresql 11.5postgresql 11 之前的版本，function 和 procedure 是一回事，但是从 11 开始就不一样了。版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # su - postgresLast login: Sat O...

os: centos 7.4db: postgresql 10.10自治事务，它将记录独立于数据库主线事务运行，并且不关心主线事务的最终结果。oracle 通过 添加 pragma autonomous_transaction 实现自治事务，可以很安全的实现写日志的安全性。参考<<Oracle 的自治事务 AUTONOMOUS TRANSACTION(https://blog....

os: centos 7.4db: postgresql 10.10自治事务，它将记录独立于数据库主线事务运行，并且不关心主线事务的最终结果。oracle 通过 添加 pragma autonomous_transaction 实现自治事务，可以很安全的实现写日志的安全性。参考<<Oracle 的自治事务 AUTONOMOUS TRANSACTION(https://blog....

os: centos 7.4db: postgresql 10.10自治事务，它将记录独立于数据库主线事务运行，并且不关心主线事务的最终结果。oracle 通过 添加 pragma autonomous_transaction 实现自治事务，可以很安全的实现写日志的安全性。参考<<Oracle 的自治事务 AUTONOMOUS TRANSACTION>>那么 po...

os: centos 7.4db: postgresql 11.5postgresql 11 之前的函数(function)和存储过程(procedure)为同义词，语法如下：CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECL...

获取当前事务的日期和/或时间∶CURRENT_DATECURRENT_TIMECURRENT_TIME (precision)CURRENT_TIMESTAMPCURRENT_TIMESTAMP (precision)LOCALTIMELOCALTIME (precision)LOCALTIMESTAMPLOCALTIMESTAMP (precision)now()tr...

关系型数据库的行列转换函数在实际应用中是相当普遍。postgresql 作为一款优秀的开源数据库，提供相关的转换函数是必须的。列=》行使用 string_agg 函数with tmp_t0 as ( select 'A'::varchar as c1 union all select 'B'::varchar as c1 union all select 'C'...

postgresql 的函数有三个状态：IMMUTABLE、STABLE、VOLATILE。这个是与其他数据库有明显概念差别的。简单描述如下： IMMUTABLE 表示该函数不能修改数据库并且对于给定的参数值总是会返回相同的值。也就是说，它不会做数据库查找或者使用没有在其参数列表中直接出现的信息。如果给定合格选项，任何用全常量参数对该函数的额调用可以立刻用该函数值替换。 STA...

先记录一下，后面再补充clock_timestamp() 和 返回实时时间值的函数， 它们的返回值会在事务中随时间的前进而变化，和 oracle 的sysdate 都是返回实时时间。pgsql 对 date 和 time 数据类型区分比较明确。select pg_typeof(clock_timestamp());-[ RECORD 1 ]--------------------...

\xselect 8/(100-3) as c1, round(8/(100-3) ,4) as c2, round(8/(100-3)::numeric ,4) as c3, 8/(100-3)::numeric as c4;-[ RECORD 1 ]--------------c1 | 0c2 | 0.0000c3 | 0.0825c4...

处理时间时用到了，记录一下。计算时间差天数select extract(day FROM (age('2017-12-10'::date , '2017-12-01'::date)));计算时间差秒数select extract(epoch FROM (now() - (now()-interval '1 day') ));...

最近开发有个小需求，就是如何比较两个数据库里指定表的数据是否一致。 自己就写了个简单函数，供大家参考。CREATE OR REPLACE FUNCTION public.f_compare_tabledata( pi_localtablename character varying, pi_localtable_excludecolumn character varying...

使用 pg_controldata 查看 Database system identifier$ /usr/pgsql-10/bin/pg_controldata -D /var/lib/pgsql/10/data/pg_control version number: 1002Catalog version number: 20170721...

postgresql 从 9.5 开始提供 rollup/cube/grouping sets 分组函数，使用起来更为方便,尤其时用sql直接出报表时，一个sql就把明细和汇总值全部搞定。https://www.postgresql.org/docs/9.5/static/sql-select.html https://www.postgresql.org/docs/9.5/static/q

有时需要手动执行一段逻辑，又不想写成函数，那么就用下面格式吧。DO LANGUAGE plpgsql $$ DECLARE lv_var varchar;BEGIN select * into lv_var from public.test_forupdate_commit(1);end;$$;或者DO $$DECLARE lv_var varcha