常用sql查询

-- 查询被锁住的表
(SELECT pid FROM pg_stat_activity where query ~ 'fzqh_3_fzqhjg_1_45947')
-- 中止进程
select pg_terminate_backend((SELECT pid FROM pg_stat_activity where query ~ 'fzqh_3_fzqhjg_1_45947' LIMIT 1));

查询被锁的语句

select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT, T.QUERY_START 
from PG_STAT_ACTIVITY T
where T.DATNAME = 'postgres' and T.WAIT_EVENT_TYPE = 'Lock';

删除时终止语句

1 2	`select` `pg_terminate_backend(pid),query` `from` `pg_stat_activity` `where` `query ~*` `'tablename'` `and` `pid <> pg_backend_pid();`

ps -ef |grep postgres |wc -l  -- 进程统计相当于连接数
 
SELECT count(*) FROM pg_stat_activity;  -- 连接数查询
 
-- 根据进程号查询sql
SELECT procpid, START, now()-START AS lap, current_query FROM (
    SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid,
    pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query  
FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' and procpid=205542
ORDER BY lap DESC;

表主键查询

select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename 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]
inner join pg_type on pg_type.oid = pg_attribute.atttypid
where pg_class.relname = 'pre_country_house_crx_data_country_dl_partition_44'
and pg_constraint.contype='p'

大表count查询

1	`Select` `reltuples::bigint` `as` `estimate_rows` `from` `pg_class` `where` `relnamespace =` `'public'::regnamespace` `and` `relname='zh_ls_4_fzztj_sum'`

　空间索引创建

　索引查询

-- 查询
select * from pg_indexes where tablename = 'l_jcsj_view_11_1';
-- 删除
DROP INDEX test_index;
-- 创建空间索引
CREATE INDEX l_zhpg_nzw_jcsj_view_11_1_gis_index ON l_jcsj_view_11_1 USING GIST (geom);

　　对已存在的表添加列和注释

1 2	`alter` `table` `tableName` `add` `COLUMN` `columnName` `varchar(50);` `comment` `on` `column` `tableName.columnName` `is` `'注释';`

　　对已存在的列设置主键

1	`ALTER` `TABLE` `tableName` `ADD` `CONSTRAINT` `xxx_xxx_primary_pk` `PRIMARY` `KEY` `(列);`

　　创建自增

DROP SEQUENCE IF EXISTS {table_name}_nid_seq;
CREATE SEQUENCE {table_name}_nid_seq
START WITH 1
INCREMENT BY 1 NO MINVALUE NO MAXVALUE
CACHE 1;
alter table trans.{table_name} alter column nid set default nextval('{table_name}_nid_seq');