postgresql_基本语法_写给自己参考的

运维相关的语句

gsql使用相关

gsql -r -h ${proxy_host} -p ${proxy_port} -U ${proxy_username} -W ${proxy_password}

数据库的剩余连接数

SELECT
max_conn - now_conn AS resi_conn
from
(
SELECT setting :: int8 AS max_conn,
(SELECT COUNT (*) FROM pg_stat_activity ) AS now_conn FROM pg_settings where NAME = 'max_connections'
)T;

当前总共正在使用的连接数

select count(1)from pg_stat_activity;

显示系统允许的最大连接数

show max_connections;

建表相关语句

主键自增长字段

id SERIAL PRIMARY KEY ,

二维的点字段

xxgeomxxx GEOMETRY(Point, 4326),

精度字段

x double precision,
y double precision

字符字段

name VARCHAR(128),

建表语句简单例子

CREATE TABLE mylocation ( 
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(Point, 4326),
  name VARCHAR(128),
  x double precision,
  y double precision
); 

创建序列

--创建一个名为serial的递增序列
CREATE SEQUENCE serial
START 101
CACHE 20;
--从序列中选出下一个数字:
SELECT nextval('serial');
--建表
CREATE TABLE t_order(my_id integer not null;);
--给t_order表的my_id绑定序列
CREATE SEQUENCE serial
START 101
CACHE 20
OWNED BY t_order.my_id;

ST_GeomFromText

可以将文本转化为坐标：
ST_GeomFromText('POINT(-0.1257 51.508)',4326)
ST_GeomFromText('POINT(0.001 0)', 4326)
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.0001 0)', 4326),'zhangsan',0.0001,0
);

ST_DistanceSphere

ST_DistanceSphere(geom, 
    ST_GeometryFromText('POINT(0 0)') --以(0,0)为中心
)  distance  ---geom 以(0,0)为中心 多少米

查询pg所有数据库信息

SELECT d.datname, 
d.oid, 
pg_get_userbyid(d.datdba) AS owner, 
shobj_description(d.oid, 'pg_database') AS comment, 
t.spcname, 
d.datacl, 
d.datlastsysoid, 
d.encoding, 
pg_encoding_to_char(d.encoding) AS encodingname 
FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace=t.oid

表空间

建表语句增加表空间

CREATE TABLE tb_medi_blank_card_record (
	card_no varchar(19) NOT NULL,
	card_seq_no varchar(3) NOT NULL,
	card_medium_code bpchar(1) NOT NULL,
	card_kind_cd bpchar(2) NOT NULL,
	dcard_categ_no varchar(4) NOT NULL,
	apply_makecard_dt varchar(8) NOT NULL,
	card_valid_dt varchar(8) NOT NULL,
	used_flag bpchar(1) NOT NULL,
	create_stamp timestamp NOT NULL,
	last_mod_stamp timestamp NOT NULL,
	optimist_lock_ver_no int4 NULL,
	CONSTRAINT t_medi_blank_card_record_pk PRIMARY KEY (card_no，card_seq_no)
) tablespace ts_busi;

给主健修改表空间

alter index t_medi_blank_card_record_pk set tablespace ts_idx;

生产设置表空间样例

按两个维度设置表空间，第一分类维度为业务、 流水； 第二个分类维度为表、 索引。
表空间命名类型说明

ts_busi 业务表表空间

ts_idx_busi 业务表索引表空间

ts_log 流水表表空间

ts_idx_log 流水表索引表空间

创建样例以相对路径方式建立tablespace;

实际路径: 高斯数据目录/pg_location/tablespace/ts_busi (例)

CREATE TABLESPACE ts_busi relative location 'tablespace/ts_busi';
CREATE TABLESPACE ts_idx_busi relative location 'tablespace/ts_idx_busi';
CREATE TABLESPACE ts_log relative location 'tablespace/ts_log'; 
CREATE TABLESPACE ts_idx_log relative location 'tablespace/ts_idx_log';


--给 dcdp 用户授于全部使用权限

GRANT ALL PRIVILEGES ON TABLESPACE ts_busi TO dcdp;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_busi TO dcdp;

GRANT ALL PRIVILEGES ON TABLESPACE ts_log TO dcdp;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_log TO dcdp;


GRANT ALL PRIVILEGES ON TABLESPACE ts_busi TO param;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_busi TO param;

GRANT ALL PRIVILEGES ON TABLESPACE ts_log TO param;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_log TO param;



GRANT ALL PRIVILEGES ON TABLESPACE ts_busi TO bss;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_busi TO bss; 

GRANT ALL PRIVILEGES ON TABLESPACE ts_log TO bss;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_log TO bss;



GRANT ALL PRIVILEGES ON TABLESPACE ts_busi TO param;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_busi TO param; 

GRANT ALL PRIVILEGES ON TABLESPACE ts_log TO param;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_log TO param;



GRANT ALL PRIVILEGES ON TABLESPACE ts_busi TO btodbl ;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_busi TO btodbl; 

GRANT ALL PRIVILEGES ON TABLESPACE ts_log TO btodbl;

GRANT ALL PRIVILEGES ON TABLESPACE ts_idx_log TO btodbl;

查询表空间

select spcname from pg_tablespace;

select pg_tablespace_size('pg default');
---查询某个表空间下有哪些业务表
select * from pg_tables where tablespace='ts_busi'