PG SQL 语法汇总

１、查询数据库大小

SELECT pg_size_pretty(pg_database_size('bspricing_sit')) As fulldbsize;

2、查看所有数据库大小

select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

3、查看所有用户

select * from pg_user;

4、查看所有角色

select * from pg_roles;

5、查询用户 XX 的权限，必须到指定的数据库下执行

select * from information_schema.table_privileges where grantee='pur_bspricing';

6、按占空间大小，顺序查看索引大小

SELECT

indexrelname,

pg_size_pretty ( pg_relation_size ( relid ) )

FROM

pg_stat_user_indexes

WHERE

schemaname = 'pur_bspricing'

ORDER BY

pg_relation_size ( relid ) DESC;

7、查询当前的日期

select statement_timestamp() :: timestamp(0) time1,CURRENT_TIMESTAMP(0) time2, now() time3

8、时间运算

select CURRENT_TIMESTAMP(0) + '1 year';

select CURRENT_TIMESTAMP(0) + '-1 year';

select CURRENT_TIMESTAMP(0) + '1 month';

select CURRENT_TIMESTAMP(0) + '30 day';

select CURRENT_TIMESTAMP(0) + '1 hour';

select CURRENT_TIMESTAMP(0) + '1 minute';

select CURRENT_TIMESTAMP(0) + '60 second';

select to_timestamp('2021-02-28 00:00:00', 'yyyy-MM-dd hh24:mi:ss') + '1 month';

9、PG 的大小写转换

select lower('HELLO');

select upper('hello');

10、创建序列

DROP SEQUENCE IF EXISTS "pur_cost"."cost_cd_request_head_s";

CREATE SEQUENCE "pur_cost"."cost_cd_request_head_s"

INCREMENT 1

MINVALUE 1

MAXVALUE 999999999999999999

START 1

CACHE 1;

COMMENT ON SEQUENCE "pur_cost"."cost_cd_request_head_s" IS '成本申报申请单头表序列';

11、获取序列的值

select nextval('test_seq') as "objectID"--获取序列下一个值

select currval('test_seq')--获取序列当前值

select lastval()--获取最近获取过的序列的值

12、批量获取序列

select nextval('ems_draft_price_detail_s') from (select generate_series(1, #{total})) s

13、查询所有的序列

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'

14、查询所有的视图

select * from pg_views where schemaname = 'hpc_cbn_integration';

15、查询表的索引以及索引对应的列

select

t.relname as table_name,

i.relname as index_name,

a.attname as column_name

from

pg_class t,

pg_class i,

pg_index ix,

pg_attribute a

where

t.oid = ix.indrelid

and i.oid = ix.indexrelid

and a.attrelid = t.oid

and a.attnum = ANY(ix.indkey)

and t.relkind = 'r'

and t.relname = 'ems_price_detail_t'

order by

t.relname,

i.relname;

16、查询表的主键对应的列

SELECT

TABLE_NAME as TABLE_NAME,

T.colname as COLUMN_NAME

FROM

information_schema.COLUMNS

LEFT JOIN (

SELECT

pg_class.relname AS tablename,

pg_attribute.attname AS colname

FROM

pg_constraint

INNER JOIN

pg_class

ON

pg_constraint.conrelid = pg_class.oid

INNER JOIN

pg_attribute

ON

pg_attribute.attrelid = pg_class.oid

AND (pg_attribute.attnum = pg_constraint.conkey [1] or

pg_attribute.attnum = pg_constraint.conkey [2] or

pg_attribute.attnum = pg_constraint.conkey [3])

WHERE

pg_constraint.contype = 'p'

) T

ON

TABLE_NAME = T.tablename

WHERE

TABLE_NAME in('ems_price_detail_t')

GROUP BY

table_schema,

TABLE_NAME,

T.colname;

17、查询表的所有的列以及列的数据类型以及字段长度

SELECT

A.attnum,

C.relname,

( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,

A.attname,

( SELECT typname FROM pg_type WHERE oid = A.atttypid ) AS TYPE,

concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS data_type

FROM

pg_catalog.pg_attribute A

inner join

pg_class C

on

A.attrelid = C.oid

inner join

pg_namespace N

on

N.oid = C.relnamespace

inner join

pg_type T

on

A.atttypid = T.oid

WHERE

1 = 1

AND C.relname = 'ems_price_detail_t'

AND A.attnum > 0

AND NOT A.attisdropped

ORDER BY A.attnum;

18、update 中带 join 的写法

update

b_item_t as a

set

name = b.name

from

b_module_t as b

where

a.class_id = b.class_id

19、批量更新的写法

update

b_item_t

set

chs_name = temp.chs_name,

name = temp.name,

chs_description = temp.chs_description,

description = temp.description

from

(values

(1, 'test1', 'test1', 'test1', 'test1'),

(2, 'test2', 'test2', 'test2', 'test2'),

(3, 'test3', 'test3', 'test3', 'test3'),

(4, 'test4', 'test4', 'test4', 'test4')

)as temp (class_id, chs_name, name, chs_description, description)

where

b_item_t.class_id = temp.class_id;

20、批量插入

insert into hpc_cbplus_maint.b_item_t(class_id, code, type, chs_name, name, chs_description, description, unit, classification_code, available, created_by, creation_date, last_updated_by, last_update_date, standard_flag, status, lifecycle_state, category)values

(0, 'test_000', 30, 'test', 'test', 'test', 'test', 'test', 'TEST', 1, -1, CURRENT_TIMESTAMP(0), -1, CURRENT_TIMESTAMP(0), 1, 1, 'GA', 'test'),

(1, 'test_001', 30, 'test', 'test', 'test', 'test', 'test', 'TEST', 1, -1, CURRENT_TIMESTAMP(0), -1, CURRENT_TIMESTAMP(0), 1, 1, 'GA', 'test'),

(2, 'test_002', 30, 'test', 'test', 'test', 'test', 'test', 'TEST', 1, -1, CURRENT_TIMESTAMP(0), -1, CURRENT_TIMESTAMP(0), 1, 1, 'GA', 'test'),

(3, 'test_003', 30, 'test', 'test', 'test', 'test', 'test', 'TEST', 1, -1, CURRENT_TIMESTAMP(0), -1, CURRENT_TIMESTAMP(0), 1, 1, 'GA', 'test'),

(4, 'test_004', 30, 'test', 'test', 'test', 'test', 'test', 'TEST', 1, -1, CURRENT_TIMESTAMP(0), -1, CURRENT_TIMESTAMP(0), 1, 1, 'GA', 'test');

21、日期转换

SELECT to_timestamp('2021-02-28 12:12:12','yyyy-MM-dd hh24:mi:ss');

SELECT to_date('2020-02-27 12:12:12','yyyy-MM-dd hh24:mi:ss') ;

22、PG 的表授权

grant usage on schema hpc_cbplus_maint to hpc_cbn_integration;

grant all on hpc_cbplus_maint.b_item_t to hpc_cbn_integration;

--异常处理

do $$

begin

grant select on all tables in schema pur_bspricing to pub_cost_it;

exception

when others then

null;

end;

$$;

23、查询 PG 数据库下所有的 schema

SELECT nspname FROM pg_namespace;

24、查询 schema 下所有的表

SELECT

C.relname,

pg_catalog.pg_get_userbyid ( C.relowner ) AS "Owner"

FROM

pg_catalog.pg_class C,

pg_catalog.pg_namespace n

WHERE

n.oid = C.relnamespace

AND n.nspname = 'hpc_cbplus_maint'

AND C.relkind = 'r';

25、复杂的 merge into

with I_PBI_COA_T_MERGE as (

update

i_pbi_coa_t as B

set

COAID = A.COAID,

COASTATUS = A.COASTATUS,

PARENTID = A.PARENTID,

COACREATIONDATE = A.COACREATIONDATE,

COALASTUPDATEDATE = A.COALASTUPDATEDATE,

AVAILABLE = A.AVAILABLE,

CREATED_BY = A.CREATED_BY,

CREATION_DATE = A.CREATION_DATE,

LAST_UPDATED_BY = A.LAST_UPDATED_BY,

LAST_UPDATE_DATE = CURRENT_TIMESTAMP(0),

COANUMBER = A.COANUMBER,

COACATEGORY = A.COACATEGORY,

PARENTTYPE = A.PARENTTYPE,

PARENTNUMBER = A.PARENTNUMBER

from

i_pbi_coa_ti as A

where

B.COAID = A.COAID

and A.PROCESSING_BATCH_NUMBER = 20201114230002

RETURNING B.*)

insert into i_pbi_coa_t(

COAID,

COASTATUS,

PARENTID,

COACREATIONDATE,

COALASTUPDATEDATE,

AVAILABLE,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

COANUMBER,

COACATEGORY,

PARENTTYPE,

PARENTNUMBER)

select T.* from(

SELECT

COAID,

COASTATUS,

PARENTID,

COACREATIONDATE,

COALASTUPDATEDATE,

AVAILABLE,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

COANUMBER,

COACATEGORY,

PARENTTYPE,

PARENTNUMBER

FROM

I_PBI_COA_TI

WHERE

PROCESSING_BATCH_NUMBER = 20201114230002) T

where not exists(select 1 from I_PBI_COA_T_MERGE where I_PBI_COA_T_MERGE.COAID = T.COAID);

26、PG 的 begin 和 end

DO $body$

begin

delete from i_pbi_coa_t where coaid = 1;

delete from i_pbi_coa_t where coaid = 2;

delete from i_pbi_coa_t where coaid = 3;

end

$body$;

27、PG 的字符串拆分为表

PlainBashC++C#CSSDiffHTML/XMLJavaJavascriptMarkdownPHPPythonRubySQL

select regexp_split_to_table('1,2,3' ,',') a;

28、字符串拆分为数组

select string_to_array('1,2,3' ,',');

29、字符串聚合

select string_agg(a,'-') from regexp_split_to_table('1,2,3' ,',') a;

30、查询正在执行的 SQL

select * from pg_stat_activity where state = 'active' ;

31、PG 表解锁

1、查看表是否被锁

SELECT A.locktype,

A.DATABASE,

A.pid,

A.MODE,

A.relation,

b.relname

FROM pg_locks A

JOIN pg_class b ON A.relation = b.oid

WHERE UPPER(b.relname) = 'EMS_PRICE_DETAIL_T';

2、查看正在运行的进程

SELECT usename,

pid,

query_start,

client_addr,

query

FROM pg_stat_activity

WHERE pid in (86418,

98945,

94228

);

3、解锁

SELECT pg_cancel_backend(94228);

32、PG 的 ON CONFLICT 用法（当记录不存在时，执行插入；否则进行更新）

INSERT INTO cost_ce_price_release_t (

release_price_id,

version_id,

item_code,

month1,

month2,

month3,

month4,

month5,

month6,

month7,

month8,

month9,

month10,

month11,

month12,

month13,

month14,

month15,

month16,

month17,

month18,

month19,

month20,

month21,

month22,

month23,

month24,

source_type,

release_type,

formula_id,

created_by,

creation_date,

last_updated_by,

last_update_date,

price_source

)

VALUES(

#{item.releasePriceId,jdbcType=VARCHAR},

#{item.versionId,jdbcType=VARCHAR},

#{item.itemCode,jdbcType=VARCHAR},

ROUND(#{item.month1,jdbcType=NUMERIC}, 6),

ROUND(#{item.month2,jdbcType=NUMERIC}, 6),

ROUND(#{item.month3,jdbcType=NUMERIC}, 6),

ROUND(#{item.month4,jdbcType=NUMERIC}, 6),

ROUND(#{item.month5,jdbcType=NUMERIC}, 6),

ROUND(#{item.month6,jdbcType=NUMERIC}, 6),

ROUND(#{item.month7,jdbcType=NUMERIC}, 6),

ROUND(#{item.month8,jdbcType=NUMERIC}, 6),

ROUND(#{item.month9,jdbcType=NUMERIC}, 6),

ROUND(#{item.month10,jdbcType=NUMERIC}, 6),

ROUND(#{item.month11,jdbcType=NUMERIC}, 6),

ROUND(#{item.month12,jdbcType=NUMERIC}, 6),

ROUND(#{item.month13,jdbcType=NUMERIC}, 6),

ROUND(#{item.month14,jdbcType=NUMERIC}, 6),

ROUND(#{item.month15,jdbcType=NUMERIC}, 6),

ROUND(#{item.month16,jdbcType=NUMERIC}, 6),

ROUND(#{item.month17,jdbcType=NUMERIC}, 6),

ROUND(#{item.month18,jdbcType=NUMERIC}, 6),

ROUND(#{item.month19,jdbcType=NUMERIC}, 6),

ROUND(#{item.month20,jdbcType=NUMERIC}, 6),

ROUND(#{item.month21,jdbcType=NUMERIC}, 6),

ROUND(#{item.month22,jdbcType=NUMERIC}, 6),

ROUND(#{item.month23,jdbcType=NUMERIC}, 6),

ROUND(#{item.month24,jdbcType=NUMERIC}, 6),

#{item.sourceType,jdbcType=NUMERIC},

#{item.releaseType,jdbcType=VARCHAR},

#{item.formulaId,jdbcType=NUMERIC},

#{item.createdBy,jdbcType=NUMERIC},

now(),

#{item.lastUpdatedBy,jdbcType=NUMERIC},

now(),

#{item.priceSource,jdbcType=NUMERIC}

)

ON conflict(item_code,version_id)

DO UPDATE SET

month1 = ROUND(#{item.month1,jdbcType=NUMERIC}, 6),

month2 = ROUND(#{item.month2,jdbcType=NUMERIC}, 6),

month3 = ROUND(#{item.month3,jdbcType=NUMERIC}, 6),

month4 = ROUND(#{item.month4,jdbcType=NUMERIC}, 6),

month5 = ROUND(#{item.month5,jdbcType=NUMERIC}, 6),

month6 = ROUND(#{item.month6,jdbcType=NUMERIC}, 6),

month7 = ROUND(#{item.month7,jdbcType=NUMERIC}, 6),

month8 = ROUND(#{item.month8,jdbcType=NUMERIC}, 6),

month9 = ROUND(#{item.month9,jdbcType=NUMERIC}, 6),

month10 = ROUND(#{item.month10,jdbcType=NUMERIC}, 6),

month11 = ROUND(#{item.month11,jdbcType=NUMERIC}, 6),

month12 = ROUND(#{item.month12,jdbcType=NUMERIC}, 6),

month13 = ROUND(#{item.month13,jdbcType=NUMERIC}, 6),

month14 = ROUND(#{item.month14,jdbcType=NUMERIC}, 6),

month15 = ROUND(#{item.month15,jdbcType=NUMERIC}, 6),

month16 = ROUND(#{item.month16,jdbcType=NUMERIC}, 6),

month17 = ROUND(#{item.month17,jdbcType=NUMERIC}, 6),

month18 = ROUND(#{item.month18,jdbcType=NUMERIC}, 6),

month19 = ROUND(#{item.month19,jdbcType=NUMERIC}, 6),

month20 = ROUND(#{item.month20,jdbcType=NUMERIC}, 6),

month21 = ROUND(#{item.month21,jdbcType=NUMERIC}, 6),

month22 = ROUND(#{item.month22,jdbcType=NUMERIC}, 6),

month23 = ROUND(#{item.month23,jdbcType=NUMERIC}, 6),

month24 = ROUND(#{item.month24,jdbcType=NUMERIC}, 6),

source_type = #{item.sourceType,jdbcType=NUMERIC},

release_type = #{item.releaseType,jdbcType=VARCHAR},

formula_id = #{item.formulaId,jdbcType=NUMERIC},

last_updated_by = #{item.lastUpdatedBy,jdbcType=NUMERIC},

last_update_date = now() ;

注意：ON conflict(item_code,version_id) 中的字段 item_code+version_id 必须建立唯一索引

33、PG 临时表的创建

PostgreSQL 支持两类临时表，会话级和事务级临时表。在会话级别的临时表中，在整个会话的生命周期中，数据一直保存。事务级临时表，数据只存在于这个事务的生命周期中。不指定临时表的属性。

PostgreSQL 中，不管是事务级还是会话级临时表，当会话结束时，临时表就会消失。这与 oracle 数据库不同，在 oracle 数据库中，只是临时表中的数据消失，而临时表还存在。

默认情况下，创建的临时表是会话级的，如果需要创建事务。需要添加“on commit delete rows”子句。

DROP TABLE IF EXISTS ems_bs_param_temp_t;

create temporary table ems_bs_param_temp_t

(

region_id int8,

item_code VARCHAR(20),

currency_code VARCHAR(20),

end_date VARCHAR(20),

owner_codeVARCHAR(20),

indemnity_item_id VARCHAR(20)

)

on commit delete rows; -- 事务级

comment on column ems_bs_param_temp_t.region_id

is '区域id';

comment on column ems_bs_param_temp_t.item_code

is '物料编码';

comment on column ems_bs_param_temp_t.currency_code

is '币种';

comment on column ems_bs_param_temp_t.end_date

is '有效期';

comment on column ems_bs_param_temp_t.owner_code

is '销售主体';

comment on column ems_bs_param_temp_t.indemnity_item_id

is 'ItemId 供SRM服务使用';

34、查询耗时长的 SQL

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

35、PG 递归查询

WITH 查询的一个重要属性是 RECURSIVE，使用 RECURSIVE 属性可以引用自己的输出，从而实现递归，一般用于层次结构或树状结构的应用场景。 如：

a、定义下面这样的表，存储每个区域（省、市、区）的 id，名字及上级区域的 pid

create table tb(id varchar(3) , pid varchar(3) , name varchar(10));

insert into tb values('002' , 0 , '浙江省');

insert into tb values('001' , 0 , '广东省');

insert into tb values('003' , '002' , '衢州市');

insert into tb values('004' , '002' , '杭州市') ;

insert into tb values('005' , '002' , '湖州市');

insert into tb values('006' , '002' , '嘉兴市') ;

insert into tb values('007' , '002' , '宁波市');

insert into tb values('008' , '002' , '绍兴市') ;

insert into tb values('009' , '002' , '台州市');

insert into tb values('010' , '002' , '温州市') ;

insert into tb values('011' , '002' , '丽水市');

insert into tb values('012' , '002' , '金华市') ;

insert into tb values('013' , '002' , '舟山市');

insert into tb values('014' , '004' , '上城区') ;

insert into tb values('015' , '004' , '下城区');

insert into tb values('016' , '004' , '拱墅区') ;

insert into tb values('017' , '004' , '余杭区') ;

insert into tb values('018' , '011' , '金东区') ;

insert into tb values('019' , '001' , '广州市') ;

insert into tb values('020' , '001' , '深圳市') ;

b、需要查出某个省，比如浙江省，管辖的所有市及市辖地区

with RECURSIVE cte as

(

select a.id,cast(a.name as varchar(100)) from tb a where id='002'

union all

select k.id,cast(c.name||'>'||k.name as varchar(100)) as name from tb k inner join cte c on c.id = k.pid

)select id,name from cte ;