PostgreSQL

pg12开始支持不区分大小写，或者区分大小写的排序的collate。其中几个关键参数： PROVIDER：指定用于与此排序规则相关的区域服务的提供程序。可能的值是: icu、libc。 默认 是libc。但若要设置大小写不敏感，目前只支持icu。 DETERMINISTIC:设置...

我们常常使用视图来限制用户只能访问部分数据，来保证数据安全。但是在pg中某些情况下即使使用了视图仍然难以保证数据的安全。使用普通用户查看表和视图： 可以看到都没有权限。You are now connected to database "postgres" as user "us...

我们在实际应用中，常常会碰到这样的场景：分组取top值，比如取某一部门收入前10的人。 一般我们都会用到窗口函数去解决这类问题，同样在pg中也支持窗口函数。例子： 创建测试表，生成10000个分组，1000万条记录。bill=# select * from (select row...

0不能作为被除数这是数学中的常识，当在数据库中除以0时，应该如何处理呢？ 在oracle和pg中是不允许被除数为0的。LOCATION: int4div, int.1 row in set, 1 warning (0.

select * from tbl where exists (select 1 from t where t.id=tbl.但是不同的写法，性能还是有一定的差距。可能对in、exists这两种写法比较熟悉的人都知道这么一个结论：IN适合于外表大而内表小的情况；EXISTS适合于...

我们想把一个查询语句返回的结果集插入到多个目标表中。例如，希望把A表的数据分别插入到 B 表、C 表和 D 表。这3个表与 A 表的结构相同(相同的列和数据类型)，并且当前不含任何数据。解决办法就是把查询结果插入到多个目标表中。insert first：对于每一行数据，只插入到第...

pg中创建函数时可以指定权限检测，有两个可选参数：SECURITY INVOKER和SECURITY DEFINER。SECURITY INVOKER：表示要用调用该函数的用户的特权来执行它。这是默认值。 SECURITY DEFINER：指定要用拥有该函数的用户的特权来执行该函...

在oracle中管道函数是用来返回行集合的，我们可以像查询物理表一样查询它或者将它的值赋给其它集合变量。管道函数为并行执行，在普通的函数中使用dbms_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端实时的输出函数执行过程中的一些信息，在...

我们在写SQL时常常会使用where和having子句来进行数据的过滤。但是这两者具体区别在哪呢？ PostgreSQL中和其它关系型数据库一样也支持聚合函数，一个聚合函数是指从多个输入行中计算出一个结果。 而对于where和having子句最大的区别在于：Where中不能使用聚...

SQL标准定义了四种隔离级别。隔离级别从低到高分别是：Read uncommitted 、Read committed 、Repeatable read 、Serializable。脏读：一个事务读取了另一个并行未提交事务写入的数据。不可重复读：一个事务重新读取之前读取过的数据，...

PostgreSQL中的’‘空字符是属于字符串类型的数据，而在oracle中，’'会默认转换成NULL，所以可以使用任何数据类型。oracle： 建表： varchar和date类型都支持’'空字符。Table created.1 row created.查询： 查询时使用is ...

PostgreSQL中的gin索引常常被用来需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索等。 但有时尽管是多值类型，我们使用gin索引查询时，却会发生查询很慢的情况。例子： 创建表并在数组类型的列上创建gin索引。接下来使用数组列进行匹配来使用索引查询(为了...

Oracle connect by语法经常用于有树形关系的记录查询，但是在pg中是不支持这一语法的，不过我们可以使用with recursive语法来实现类似的功能。 除此之外，pg自带的tablefunc插件中的connectby函数也可以达到类似的效果。

PostgreSQL从8.2版本就开始支持advisory lock这一功能。那么什么是advisory lock呢？官方文档的解释是： PostgreSQL提供了一种方法创建由应用定义其含义的锁。这种锁被称为咨询锁，因为系统并不强迫其使用——而是由应用来保证其正确的使用。 ad...

我们在优化SQL的时候常常用到的手段就是加索引，但是随便增加索引可能会带来一系列问题：写入IO放大，占用更多空间，写入性能下降。并且，在加索引时，会堵塞DML。虽然PG支持并发加索引，不堵塞DML。 因此我们要怎么判断该不该加索引呢？ 虚拟索引是一个很有用的东西，没有副作用，只是...

JIT(just in time)即时编译是指将某种形式的解释程序计算转变成原生程序的过程，并且这一过程是在运行时完成的。 pg11开始支持这一功能，目前支持tuple deform（将磁盘上的tuple转换为内存中TUPLE格式），以及表达式（select, where, 等语...

PostgreSQL9.4开始支持SQL标准中的WITH ORDINALITY写法，用来返回记录的每一行行号。 当 from语句后面的函数加上 WITH ORDINALITY 属性后，那么返回的结果集将增加一个整数列，这个整数列从1开始，并且按 1 递增。2、不支持场景 目前不支...

oracle中的dbms_sql包可以用来执行动态SQL，让我们在存储过程的动态SQL中使用prepared statement。dept_no_array DBMS_SQL.c := DBMS_SQL.DBMS_SQL.PARSE(c, stmt, DBMS_SQL.DBMS_...

keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数。使用KEEP 时和DENSE_RANK FIRST /DENSE_RANK LAST一起使用，获取一组中排名第一或者排名最后的记录。必须有order by 子句用来排序。后面也可以接over...

PostgreSQL的file_fdw模块允许我们直接从数据库中来访问服务器的文件系统中的文件，不过这些文件必须是能够被COPY FROM读取的格式。filename：指定要被读取的文件。必须是一个绝对路径名。 必须指定filename或program， 但不能 同时指定两个。 ...