shan blog
Posts Tags Categories Docs About
shan blog

Contents

pg

 included in db
   703 words   4 minutes    views  

关于pg

BSD协议

默认情况下 PostgreSQL 安装完成后，自带了一个命令行工具 SQL Shell(psql)

  • Windows 系统一般在它的安装目录下： C:\Program Files\PostgreSQL\11\bin\psql.exe psql的命令都以\开始

psql语法

psql的命令都以\开始，；结束

PostgreSQL 客户端应用

https://www.yiibai.com/manual/postgresql/sql.html

PostgreSQL操作-psql基本命令

1 建立数据库连接

  • psql -h IP地址 -p 端口 -U 数据库名

2 访问数据库

1、列举数据库：\l 2、选择数据库：\c 数据库名 3、查看该某个库中的所有表：\dt select * from pg_tables where schemaname = ‘public’; 4、切换数据库：\c interface

​ 查看当前数据库:\c

?SELECT column_name FROM information_schema.columns WHERE table_name =‘table_name’;

5、查看某个库中的某个表结构：\d 表名 6、查看某个库中某个表的记录：select * from apps limit 1; 7、显示字符集：\encoding 8、退出psgl：\q

类型

PostgreSQL数据类型 - 简书

官方 https://www.postgresql.org/docs/10/datatype.html

postgresql 10 数据类型 (完整版) - 走看看

索引

主键 http://www.postgres.cn/docs/10/ddl-constraints.html

PostgreSQL 索引

序列

postgresql-创建主键自增的表

参考

  • 创建表的时候创建自增

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    create table test_a (
 id serial,
 name character varying(128),
 constraint pk_test_a_id primary key( id)
);
或者
create table test_b(
  id serial PRIMARY KEY,
  name character varying(128)
);

    上面这两种方法用的是pg的serial类型实现自增，drop表的时候指定的序列也会drop掉

  • 表已经创建

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

    create table test_c (
  id integer PRIMARY KEY,
  name character varying(128)
);  
再手动创建序列
CREATE SEQUENCE test_c_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
将序列赋值给主键
alter table test_c alter column id set default nextval('test_c_id_seq');

    这种方法在drop表的时候序列不会随着drop掉

序列相关函数

官网

中文

函数 返回类型 描述
currval(regclass) bigint 返回最近一次用nextval获取的指定序列的值
lastval() bigint 返回最近一次用nextval获取的任何序列的值
nextval(regclass) bigint 递增序列并返回新值
setval(regclass, bigint) bigint 设置序列的当前值
setval(regclass, bigint, boolean) bigint 设置序列的当前值以及is_called标志

索引

什么是索引？

数据库索引的重要特点

  • 索引使用SELECT查询和WHERE子句加速数据输出，但是会减慢使用INSERT和UPDATE语句输入的数据。

  • 您可以在不影响数据的情况下创建或删除索引。

  • 可以通过使用CREATE INDEX语句创建索引，指定创建索引的索引名称和表或列名称。

  • 还可以创建一个唯一索引，类似于唯一约束，该索引防止列或列的组合上有一个索引重复的项。

PostgreSQL创建索引

postgresql 建立索引

CREATE INDEX语句用于创建PostgreSQL索引。

语法

1

CREATE INDEX index_name ON table_name;

索引类型

PostgreSQL中有几种索引类型，如B-tree，Hash，GiST，SP-GiST和GIN等。每种索引类型根据不同的查询使用不同的算法。 默认情况下，CREATE INDEX命令使用B树索引。

索引的操作

  • 查看索引

select * from pg_indexes where tablename=‘tbname’;

  • 或者

select * from pg_statio_all_indexes where relname=‘tbname’;

  • PostgreSQL删除索引

DROP INDEX index_name;

什么时候应该避免使用索引？

  • 应该避免在小表上使用索引。

  • 不要为具有频繁，大批量更新或插入操作的表创建索引。

  • 索引不应用于包含大量NULL值的列。

  • 不要在经常操作(修改)的列上创建索引。

备份

有时候需要字段名双引号，使用 –quote-all-identifiers，去掉拥有者 使用 -O pg_dump.exe -O –quote-all-identifiers –file “E:\00Workspace\00GeoTools\07pg\portal\0528-2.sql” –host “localhost” –port “5432” –username “postgres” –no-password –verbose –format=p –schema-only –create –encoding “UTF8” “db_geoscene_system”

备份单表

pg_dump -s -t tlb exampledb > /tmp/tlb

pg_dump -h host -p port -U username -s -t tablename dbname > struct.sql

pg_dump -U postgres -s -t tb_serviceitem db_geoscene_system> tb_serviceitem .sql

D:\Program Files\pgAdmin 4\v5\runtime\pg_dump.exe –file “E:\06db\2\tb_serviceitem .sql " –host “127.0.0.1” –port “5432” –username “postgres” –no-password –verbose –format=c –schema-only –table “resource.tb_serviceitem” “db_geoscene_system”

D:\Program Files\pgAdmin 4\v5\runtime\pg_dump.exe –file “E:\06db\2\tb_serviceitem .sql” –host “127.0.0.1” –port “5432” –username “postgres” –no-password –verbose –format=p –schema-only –table “resource.tb_serviceitem” “db_geoscene_system”

pg_dump -f tb_serviceitem .sql -U postgres -s -t “resource.tb_serviceitem” “db_geoscene_system”

触发器

触发器是一种由事件自动触发执行的特殊存储过程，这些事件可以是对一个表进行 INSERT、UPDATE、DELETE 等操作。

触发器经常用于加强数据的完整性约束和业务规则上的约束等。

查看触发器

1

select * from information_schema.triggers

PostgreSQL触发器(一)创建触发器

参考 易百教程 https://www.yiibai.com/postgresql

https://www.runoob.com/postgresql/postgresql-tutorial.html

http://www.postgres.cn/index.php/v2/home

用户

修改密码 ALTER USER postgres WITH PASSWORD ‘xxx’;

datatype

postgres的几何数据类型（非postgis类型）

8.8. Geometric Types

  • Points
  • Lines
  • Line Segments
  • Boxes
  • Paths
  • Polygons
  • Circles

point

select ‘1,1’::point; select ‘(1,1)'::point;

line

select ‘1,1,2,2’::line; select ‘(1,1),(2,2)'::line; select ‘((1,1),(2,2))'::line; select ‘[(1,1),(2,2)]'::line;

select line'1,1,2,2’; select line’(1,1),(2,2)'; select line’((1,1),(2,2))'; select line’[(1,1),(2,2)]';

lseg

select ‘1,1,2,2’::lseg; select ‘(1,1),(2,2)'::lseg; select ‘((1,1),(2,2))'::lseg; select ‘[(1,1),(2,2)]'::lseg;

select lseg'1,1,2,2’; select lseg’(1,1),(2,2)'; select lseg’((1,1),(2,2))'; select lseg'[(1,1),(2,2)]';

box

select ‘1,1,2,2’::box; select ‘(1,1),(2,2)'::box; select ‘((1,1),(2,2))'::box;

select box'1,1,2,2’; select box’(1,1),(2,2)'; select box'((1,1),(2,2))';

path

select ‘1,1,2,2,3,3,4,4’::path; select ‘(1,1),(2,2),(3,3),(4,4)'::path; select ‘((1,1),(2,2),(3,3),(4,4))'::path;

select path'1,1,2,2,3,3,4,4’; select path’(1,1),(2,2),(3,3),(4,4)'; select path'((1,1),(2,2),(3,3),(4,4))';

polygon

select ‘1,1,2,2,3,3,4,4’::polygon; select ‘(1,1),(2,2),(3,3),(4,4)'::polygon; select ‘((1,1),(2,2),(3,3),(4,4))'::polygon;

select polygon'1,1,2,2,3,3,4,4’; select polygon’(1,1),(2,2),(3,3),(4,4)'; select polygon'((1,1),(2,2),(3,3),(4,4))';

circle

select ‘1,1,5’::circle; select ‘((1,1),5)'::circle; select ‘<(1,1),5>'::circle;

select circle'1,1,5’; select circle’((1,1),5)'; select circle'<(1,1),5>';

functions-geometry

参考：几何类型,操作符,基本函数整理

​ PostgreSQL中的几何类型

postgis

官网

几何数据类型

PostGIS支持所有OGC规范的“Simple Features”类型，同时在此基础上扩展了对3DZ、3DM、4D坐标的支持。

1. OGC的WKB和WKT格式

OGC定义了两种描述几何对象的格式，分别是WKB（Well-Known Binary）和WKT（Well-Known Text）。

在SQL语句中，用以下的方式可以使用WKT格式定义几何对象：

POINT(0 0) ——点

LINESTRING(0 0,1 1,1 2) ——线

POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) ——面

MULTIPOINT(0 0,1 2) ——多点

MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4)) ——多线

MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))) ——多面

GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4))) ——几何集合

以下语句可以使用WKT格式插入一个点要素到一个表中，其中用到的GeomFromText等函数在后面会有详细介绍：

INSERT INTO table ( SHAPE, NAME )

VALUES ( GeomFromText(‘POINT(116.39 39.9)’, 4326), ‘北京’);

2. EWKT、EWKB和Canonical格式

EWKT和EWKB相比OGC WKT和WKB格式主要的扩展有3DZ、3DM、4D坐标和内嵌空间参考支持。

以下以EWKT语句定义了一些几何对象：

POINT(0 0 0) ——3D点

SRID=32632;POINT(0 0) ——内嵌空间参考的点

POINTM(0 0 0) ——带M值的点

POINT(0 0 0 0) ——带M值的3D点

SRID=4326;MULTIPOINTM(0 0 0,1 2 1) ——内嵌空间参考的带M值的多点

以下语句可以使用EWKT格式插入一个点要素到一个表中：

INSERT INTO table ( SHAPE, NAME )

VALUES ( GeomFromEWKT(‘SRID=4326;POINTM(116.39 39.9 10)'), ‘北京’ )

Canonical格式是16进制编码的几何对象，直接用SQL语句查询出来的就是这种格式。

3. SQL-MM格式

SQL-MM格式定义了一些插值曲线，这些插值曲线和EWKT有点类似，也支持3DZ、3DM、4D坐标，但是不支持嵌入空间参考。

以下以SQL-MM语句定义了一些插值几何对象：

CIRCULARSTRING(0 0, 1 1, 1 0) ——插值圆弧

COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1)) ——插值复合曲线

CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1)) ——曲线多边形

MULTICURVE((0 0, 5 5),CIRCULARSTRING(4 0, 4 4, 8 4)) ——多曲线

MULTISURFACE(CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1)),((10 10, 14 12, 11 10, 10 10),(11 11, 11.5 11, 11 11.5, 11 11))) ——多曲面

约束

B-tree 和 GiST 索引方法支持多字段索引

只有 B-tree 支持唯一索引

https://gis.stackexchange.com/questions/270769/enforce-postgis-constraint-that-geometries-do-not-overlap

https://blog.csdn.net/cpongo1/article/details/89542259

Updated on 2022-12-11  3a74709
Read Markdown
Back | Home