-- 修改表字段类型 alter table '表名' alter COLUMN '字段名' type '要修改的字段类型' ; -- 添加表字段 ALTER TABLE '表名' ADD '字段名' '要修改的字段类型'; -- 删除表字段 ALTER TABLE '表名' DROP '字段名'; -- 表的重命名 ALTER TABLE '表名' RENAME TO '新表名'; -- 更改列的名字 ALTER TABLE '表名' RENAME '字段名' to '新字段名';
标签: postgresql
PostgreSQL创建只读账号
-- 回收创建表的权限 revoke create on schema public from public; -- 创建账号 create user readonly with encrypted password 'pwd'; alter user readonly set default_transaction_read_only=on; -- 给只读账号连接数据的权限 grant connect on database 数据库名 to readonly; -- 在指定数据库下执行 grant usage on schema 可配置多个schema to readonly; grant select on all sequences in schema public to readonly; grant select on all tables in schema public to readonly; -- 新建的表也要给只读账号权限 alter default privileges in schema public grant select on tables to readonly;
说明:只做记录备用,未测试; 该命令存在缺陷,如一个库存在多个schema需一一赋予权限,可能并非是最好的处理方式
PostgreSQL物化视图
-- 物化视图用于固定查询的结果 -- 需要定期更新,相当于重新执行查询命令 -- 主要用于替代查询时间过长且数据不会发生很大变化的情况 -- 创建语法: CREATE MATERIALIZED VIEW '视图名称' AS '查询命令'; -- 更新语法: REFRESH MATERIALIZED VIEW '视图名称'
PostgreSQL查询数据库的连接信息
select * from pg_stat_activity; -- pg_stat_activity为系统视图 -- 可根据where限定数据库名,连接ip,表名等字段查询相关信息
query_start:active状态的查询开始时间,如果状态不是active的,那么就是最后一次查询开始的时间 state:运行状态,可以为几种值。 active:正在执行查询; idle:等待新的命令; idle in transaction:后端是一个事务,但是尚未执行查询; idle in transaction(aborted):和idle in transaction类似,除了事务执行出错。 query:执行的查询文本(即SQL)。 如果状态是active,那么就是正在执行的SQL; 如果是其他状态,则展示最后一次执行的SQL。 所以可以用: select count(*) from pg_stat_activity where state='idle'; 查询闲置连接数。如果数字过大,可以认为是有问题的(如连接忘记关闭)。 如果想进一步定位到有问题的SQL,可以如下查询: select query,count(*) as num from pg_stat_activity where state='idle' group by query order by num desc;
杀掉进程 SELECT pg_terminate_backend(PID); 这种可以kill掉各种操作(select、update、delete、drop等)操作
PostgreSQL跨库联查
--导入支持(windows下使用,其他环境不支持) create extension dblink --方法一(缺陷:默认端口号5432) select * from dblink ( 'host=$IP port=$端口号 dbname=$数据库名 user=$用户名 password=$密码','$sql') as a( $表结构(类似于建表命令 字段名 字段类型,....) ) --方法二 --建立连接 SELECT dblink_connect('$连接名(自定义)','host=$IP dbname=$数据库名 user=$用户名 password=$密码'); --查看连接 select dblink_get_connections(); --断开所有连接 select dblink_disconnect(); --断开指定连接 select dblink_disconnect('$连接名'); --查询(如创建连接时没有定义连接名,则此处可省略) SELECT * FROM dblink('$连接名','$sql')AS a ($表结构);
PostgreSQL查询数据库中所有表的行数
--联合两层schema并比较对应表的行数是否相等(需在vacuum后才准确,否则会有增删浮动) SELECT a.relname,a.reltuples merge,b.reltuples center,a.reltuples=b.reltuples FROM ( SELECT relname, reltuples FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND n.nspname = 'merge' ) a FULL JOIN ( SELECT relname, reltuples FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND n.nspname = 'center' ) b ON a.relname=b.relname
postgresql根据结果集中某字段限定该字段相同值只获取20条
SELECT T .company_id, T .business_description_cn, T .business_scope_cn, T .company_tag_cn, T .industry_id, T .industry, T .company_name_cn FROM ( SELECT company_id, business_description_cn, business_scope_cn, company_tag_cn, industry_id, config."dictionary".name_cn AS industry, company_name_cn, ROW_NUMBER() OVER(PARTITION BY industry_id) AS ROW FROM company_profile LEFT JOIN config."dictionary" ON company_profile.industry_id = config."dictionary". ID WHERE industry_id IS NOT NULL AND company_status_id LIKE '2%' ) T WHERE ROW <= 20
postgresql创建简单搜索视图
--创建search_company CREATE MATERIALIZED VIEW search_company AS SELECT DISTINCT a.company_id, a.ticker, a.company_status_id, a.company_name_cn, a.company_name_en, a.company_shortname_cn, a.company_shortname_en, b.brand_name, a.year_founded, c.name_cn AS company_status_name_cn, c.name_en AS company_status_name_en FROM ((company_profile a LEFT JOIN brand_info b ON (((a.company_id)::text = (b.company_id)::text))) LEFT JOIN config.dictionary c ON (((a.company_status_id)::text = (c.id)::text))) WHERE a.company_status_id!='3.3' ORDER BY a.ticker; --导入自定义函数(扩展包) CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser); ALTER TEXT SEARCH CONFIGURATION zhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; create extension pg_trgm; create or replace function record_to_text(anyelement) returns text as $$ select $1::text; $$ language sql strict immutable; --创建全文索引 create index idx_search_company_gist on search_company using gist (record_to_text(search_company) gist_trgm_ops); create index idx_search_company_gin on search_company using gin (record_to_text(search_company) gin_trgm_ops); --检测索引是否正常 /*+ BitmapScan(search_company idx_search_company_gin) */ select * from search_company where record_to_text(search_company) ~ '经纬' and company_status_id like'3%' limit 99;
同步同一个表中的相关字段
--创建函数 CREATE OR REPLACE FUNCTION updateItemDDL(center_show varchar,item_ddl VARCHAR,table_name varchar,item varchar) RETURNS void as $$ UPDATE schema_table set center_show=$1,item_ddl=$2 WHERE "table_name"=$3 AND item=$4 AND "version"='v2'; $$ LANGUAGE 'sql' VOLATILE; --执行函数 SELECT updateItemDDL(center_show,item_ddl,"table_name",item) from schema_table WHERE "version"='v1' AND item_ddl!=''; 查询是否执行成功 SELECT "table_name",item,center_show,item_ddl from schema_table WHERE "version"='v2' AND item_ddl!=''