Loading... # PostgreSQL 常用函数 ## 数据库类型转换 ```sql -- 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. 主键自增设为当前最大值 ```sql -- 设自增主键从表最大id值开始 ( 将 tablename 换成具体的表名即可 ) select setval('tablename_id_seq',(select max(id) from <tablename>)) ``` ### 2. 更新表统计信息 ```sql -- ANALYZE 命令更新表统计信息, 添加 VERBOSE 参数会显示关于分析进度的详细信息, -- 但是可能会导致输出信息较多,特别是对于大型表来说。这可能会影响命令的执行时间和输出结果的大小。 -- 因此,建议在需要详细信息时使用 VERBOSE 参数,而在普通情况下可以省略该参数。 ANALYZE [VERBOSE] [table_name] ``` ### 3. 创建备份表 ```sql CREATE TABLE tablename_bak AS SELECT * FROM tablename WHERE 1=2; INSERT INTO tablename_bak SELECT * FROM tablename; ``` ### 4. 分组时合并字段 ```sql -- 分组时合并某个字段, 用分隔符隔开 string_agg(expression, delimiter) ``` ### 5. 数组中查询具体某一个值 ```sql -- 从 1,12,3 中查询具体某一个值 string_to_array(<value>, ',') && string_to_array(<column>, ',') ``` ### 6. 增加表索引 ```sql -- 增加表索引 create index <indexName> on <table_name>(xxx, xxx, xxx); ``` ### 7. 增加表字段 ```sql -- 增加表字段 alter table <table_name> add column size bigint; comment on column tbl_fsl_system_files.size is '新增字段xxx'; ``` ### 8. 修改表字段类型 ```sql -- 修改表字段类型 ALTER TABLE <table_name> ALTER COLUMN <column_name> TYPE varchar(512); ``` ### 9. 查询客户端连接 ```sql -- 查询客户端连接, 可以筛选ip指定ip select * from pg_stat_activity where application_name = 'PostgreSQL JDBC Driver' -- and client_addr = '10.6.59.61' ; ``` ### 10. 清除客户端连接 ```sql -- 清除客户端连接 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. 查看锁表 ```sql -- 查看锁表 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. 查询表字段和注释 ```sql -- 查询表字段和注释 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. 日期类型 ```sql --------------------------------------------------------------------------------- 名字 描述 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是一个标识符或字符串,是从源数据中的抽取的域。 ```sql -- 函数格式: extract (field from source) ``` ```sql 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) --------------------- ``` ```sql -- 计算时间差天数 select extract(day FROM (age('2017-12-10'::date , '2017-12-01'::date))); ``` ```sql -- 计算时间差秒数 select extract(epoch FROM (now() - (now()-interval '1 day') )); ``` 最后修改:2023 年 07 月 07 日 © 允许规范转载 赞 2 如果觉得我的文章对你有用,请点个赞吧~