PostgreSQL 常用函数（持续更新）

前言

记录一些 PostgreSQL 的常用函数

字符串

字符串/字段拼接

string_agg

string_agg：用于拼接字段，需要两个参数，第一个是需要进行拼接的字段名，第二个是用于拼接字符的拼接符号

select string_agg(a,', ') from tab1;

如果字段不是字符类型，例如字段是数字类型，需要先转换为字符才能进行拼接，否则会报错

|| ：和 Oracle 一样，可以用于拼接字符或者字段，如果是不是拼接的字段，字符串需要在两个单引号内

select a || ',' || b from tab1 ;
select 'a' || ',' || 'b' ;

类型转换

https://www.postgresql.org/docs/current/sql-createcast.html

可以使用 cast 函数或者双冒号 ::，在PG 的SQL中，会经常看到 “::” 的语法， “::” 符号其实是一个显示的类型转换符，作用等同于 CAST。

select a::text from tab1;
select 1::text;

select cast(a as text) from tab1;
select cast(1 as text);

文件信息

pg_relation_filepath() 根据OID或名称返回关系对应的文件路径

select pg_relation_filepath('tab1');

select schemaname, tablename, pg_relation_filepath(tablename::text) from pg_tables where schemaname = '&schemaname';

一般情况下对象的 oid 和 relfilenode 是相同的，但是表和索引的relfilenode值会被一些命令（例如TRUNCATE，REINDEX，CLUSTER）所改变。 例如对表 sampletbl 执行 TRUNCATE ， PostgreSQL 会为表分配一个新的 relfilenode 。

127.0.0.1:54323; wxj@db1 > select pg_relation_filepath('tab1');
+----------------------+
| pg_relation_filepath |
+----------------------+
| base/16389/16544     |
+----------------------+

127.0.0.1:54323; wxj@db1 > alter table tab1 rename to t1;
ALTER TABLE
Time: 16.013 ms
127.0.0.1:54323; wxj@db1 > select pg_relation_filepath('t1');
+----------------------+
| pg_relation_filepath |
+----------------------+
| base/16389/16544     |
+----------------------+
(1 row)

Time: 0.968 ms
127.0.0.1:54323; wxj@db1 > create table tab1 as select * from t1;
SELECT 6
Time: 5.227 ms
127.0.0.1:54323; wxj@db1 > select pg_relation_filepath('tab1');
+----------------------+
| pg_relation_filepath |
+----------------------+
| base/16389/16547     |
+----------------------+
(1 row)
127.0.0.1:54323; wxj@db1 > select oid, relname, relfilenode from pg_class where relname in ('t1','tab1');
+-------+---------+-------------+
|  oid  | relname | relfilenode |
+-------+---------+-------------+
| 16544 | t1      |       16544 |
| 16547 | tab1    |       16547 |
+-------+---------+-------------+
(2 rows)

127.0.0.1:54323; wxj@db1 > TRUNCATE TABLE  t1;
TRUNCATE TABLE
Time: 4.922 ms
127.0.0.1:54323; wxj@db1 > select oid, relname, relfilenode from pg_class where relname in ('t1','tab1');
+-------+---------+-------------+
|  oid  | relname | relfilenode |
+-------+---------+-------------+
| 16547 | tab1    |       16547 |
| 16544 | t1      |       16550 |
+-------+---------+-------------+
(2 rows)

rename table/database 对表或者 db 进行重命名操作都不会变更 relfilenode

127.0.0.1:54323; wxj@demo > select schemaname, tablename, pg_relation_filepath(tablename::text) from pg_tables where schemaname = 'bookings';
+------------+-----------------+----------------------+
| schemaname |    tablename    | pg_relation_filepath |
+------------+-----------------+----------------------+
| bookings   | ticket_flights  | base/16417/16467     |
| bookings   | boarding_passes | base/16417/16440     |
| bookings   | aircrafts_data  | base/16417/16421     |
| bookings   | flights         | base/16417/16446     |
| bookings   | airports_data   | base/16417/16431     |
| bookings   | seats           | base/16417/16463     |
| bookings   | tickets         | base/16417/16472     |
| bookings   | bookings        | base/16417/16443     |
+------------+-----------------+----------------------+

127.0.0.1:54323; wxj@demo > alter database demo rename to demo01;
ERROR:  current database cannot be renamed
Time: 1.187 ms
127.0.0.1:54323; wxj@demo > \c testdb
You are now connected to database "testdb" as user "wxj".
127.0.0.1:54323; wxj@testdb > alter database demo rename to demo01;
ALTER DATABASE
Time: 2.038 ms
127.0.0.1:54323; wxj@testdb > select schemaname, tablename, pg_relation_filepath(tablename::text) from pg_tables where schemaname = 'bookings';
+------------+-----------+----------------------+
| schemaname | tablename | pg_relation_filepath |
+------------+-----------+----------------------+
+------------+-----------+----------------------+
(0 rows)

Time: 75.493 ms
127.0.0.1:54323; wxj@testdb > \c demo01
You are now connected to database "demo01" as user "wxj".
127.0.0.1:54323; wxj@demo01 > select schemaname, tablename, pg_relation_filepath(tablename::text) from pg_tables where schemaname = 'bookings';
+------------+-----------------+----------------------+
| schemaname |    tablename    | pg_relation_filepath |
+------------+-----------------+----------------------+
| bookings   | ticket_flights  | base/16417/16467     |
| bookings   | boarding_passes | base/16417/16440     |
| bookings   | aircrafts_data  | base/16417/16421     |
| bookings   | flights         | base/16417/16446     |
| bookings   | airports_data   | base/16417/16431     |
| bookings   | seats           | base/16417/16463     |
| bookings   | tickets         | base/16417/16472     |
| bookings   | bookings        | base/16417/16443     |
+------------+-----------------+----------------------+
(8 rows)

Time: 7.055 ms