PostgreSQL 常用函数
数据库类型转换
-- Oracle 数据库类型对应 PostgreSQL
CHAR(n) -----> CHAR(n)
VARCHAR2(n) -----> VARCHAR(n)
NUMBER(n, m) -----> NUMERIC(n, m)
NUMBER(4) -----> SMALLINT
NUMBER(9) -----> INT
NUMBER(18) -----> BIGINT
NUMBER(n) -----> NUMERIC(n)
DATE -----> TIMESTAMP(0)
CLOB -----> TEXT
TIMESTAMP WITH LOCAL TIME ZONE -----> TIMESTAMPTZ常用函数
1. 主键自增设为当前最大值
-- 设自增主键从表最大id值开始 ( 将 tablename 换成具体的表名即可 )
select setval('tablename_id_seq',(select max(id) from <tablename>))2. 更新表统计信息
-- ANALYZE 命令更新表统计信息, 添加 VERBOSE 参数会显示关于分析进度的详细信息,
-- 但是可能会导致输出信息较多，特别是对于大型表来说。这可能会影响命令的执行时间和输出结果的大小。
-- 因此，建议在需要详细信息时使用 VERBOSE 参数，而在普通情况下可以省略该参数。
ANALYZE [VERBOSE] [table_name]3. 创建备份表
CREATE TABLE tablename_bak AS SELECT * FROM tablename WHERE 1=2;
INSERT INTO tablename_bak SELECT * FROM tablename;4. 分组时合并字段
-- 分组时合并某个字段, 用分隔符隔开
string_agg(expression, delimiter)5. 数组中查询具体某一个值
-- 从 1,12,3 中查询具体某一个值
string_to_array(<value>, ',') && string_to_array(<column>, ',')6. 增加表索引
-- 增加表索引
create index <indexName> on <table_name>(xxx, xxx, xxx);7. 增加表字段
-- 增加表字段
alter table <table_name> add column size bigint;
comment on column tbl_fsl_system_files.size is '新增字段xxx';8. 修改表字段类型
-- 修改表字段类型
ALTER TABLE <table_name> ALTER COLUMN <column_name> TYPE varchar(512);9. 查询客户端连接
-- 查询客户端连接, 可以筛选ip指定ip
select * from pg_stat_activity where application_name = 'PostgreSQL JDBC Driver'
-- and client_addr = '10.6.59.61' ;10. 清除客户端连接
-- 清除客户端连接
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname='fsl'
AND pid<>pg_backend_pid() and client_addr = '10.6.59.61';11. 查看锁表
-- 查看锁表
select pid from pg_locks where relation in (select oid from pg_class where relname='tbl_fsl_org_user');
-- 解锁表
select pg_terminate_backend('<上面查询到的pid>');
-- 查看锁表和锁表语句 (第一种方式)
SELECT pg_stat_activity.pid, pg_stat_activity.datname, pg_stat_activity.usename, pg_stat_activity.application_name, pg_stat_activity.query, pg_locks.relation::regclass AS locked_table
FROM pg_stat_activity
JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_locks.mode = 'ExclusiveLock' AND pg_locks.granted = true;
-- 查看锁表和锁表语句 (第二种方式)
SELECT pid, datname, usename, application_name, backend_start, state, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '<IDLE>%';12. 查询表字段和注释
-- 查询表字段和注释
with tmp_tab as (select pc.oid as ooid
, pn.nspname
, pc.*
from pg_class pc
left outer join pg_namespace pn on pc.relnamespace = pn.oid
where 1 = 1
and pc.relkind in ('r')
and pn.nspname not in ('pg_catalog', 'information_schema') -- select pn.oid, pn.* from pg_namespace pn where 1=1
and pn.nspname not like 'pg_toast%'
and pc.oid not in
(select inhrelid
from pg_inherits)
and pc.relname not like '%peiyb%'
order by pc.relname)
, tmp_col as (select pa.*
from pg_attribute pa
where 1 = 1
--and pa.attrelid = 168605
and pa.attname not in ('tableoid', 'cmax', 'xmax', 'cmin', 'xmin', 'ctid'))
, tmp_desc as (select pd.*
from pg_description pd
where 1 = 1
and pd.objsubid <> 0 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为0。
--and pd.objoid=168605
)
select t0.*
from (select tab.nspname,
tab.relname,
tc.attname,
tc.attnum,
de.description,
'comment on COLUMN ' || tab.nspname || '.' || tab.relname || '.' || tc.attname || ' is ''' ||
de.description || ''';' as column_description
from tmp_tab tab
left outer join tmp_col tc
on tab.ooid = tc.attrelid
left outer join tmp_desc de
on tc.attrelid = de.objoid
and tc.attnum = de.objsubid) t0
where 1 = 1
--and t0.description is not null
and relname = '<table_name>'
order by t0.nspname, t0.relname, t0.attnum;13. 日期类型
---------------------------------------------------------------------------------
名字 描述
timestamp [ § ] [ without time zone ] 包括日期和时间（无时区）
timestamp [ § ] with time zone 包括日期和时间，有时区
date 日期（没有一天中的时间）
time [ § ] [ without time zone ] 一天中的时间（无日期）
time [ § ] with time zone 仅仅是一天中的时间（无日期），带时区
interval [ fields ] [ § ] 时间间隔
---------------------------------------------------------------------------------13. 日期函数 extract
extract函数是从日期或者时间数值里面抽取子域，比如年、月、日等。source必须是timestamp、time、interval类型的值表达式。field是一个标识符或字符串，是从源数据中的抽取的域。
-- 函数格式：
extract （field from source）1. century （世纪）
test=# select extract (century from timestamp '2017-07-31 22:18:00');
date_part
-----------
21
(1 row)
2. year （年）
test=# select extract (year from timestamp '2017-07-31 22:18:00');
date_part
-----------
2017
(1 row)
3. decade （得到年份除10的值）
test=# select extract (decade from timestamp '2017-07-31 22:18:00');
date_part
-----------
201
(1 row)
4. millennium（得到第几个千年，0-1000第一个，1001-2000第二个，2001-3000第三个）
test=# select extract (millennium from timestamp '2017-07-31 22:18:00');
date_part
-----------
3
(1 row)
5. quarter （季度）
test=# select extract (quarter from timestamp '2017-07-31 22:18:00');
date_part
-----------
3
(1 row)
6. month （月份）
test=# select extract (month from timestamp '2017-07-31 22:18:00');
date_part
-----------
7
(1 row)
test=# select extract (month from interval '2 years 11 months');
date_part
-----------
11
(1 row)
7. week （返回当前是几年的第几个周）
test=# select extract (week from timestamp '2017-07-31 22:18:00');
date_part
-----------
31
(1 row)
8. dow （返回当前日期是周几，周日：0，周一：1，周二：2，...）
test=# select extract (dow from timestamp '2017-07-31 22:18:00');
date_part
-----------
1
(1 row)
9. day （本月的第几天）
test=# select extract (day from timestamp '2017-07-31 22:18:00');
date_part
-----------
31
(1 row)
10. doy （本年的第几天）
test=# select extract (doy from timestamp '2017-07-31 22:18:00');
date_part
-----------
212
(1 row)
11. hour （小时）
test=# select extract (hour from timestamp '2017-07-31 22:18:00');
date_part
-----------
22
(1 row)
12. min （得到时间中的分钟）
test=# select extract (min from timestamp '2017-07-31 22:18:00');
date_part
-----------
18
(1 row)
13. sec （返回时间中的秒）
test=# select extract (sec from timestamp '2017-07-31 22:18:00');
date_part
-----------
0
(1 row)
----------------------- 计算时间差天数
select extract(day FROM (age('2017-12-10'::date , '2017-12-01'::date)));-- 计算时间差秒数
select extract(epoch FROM (now() - (now()-interval '1 day') ));