postgreSql数据库笔记

postgreSql数据库笔记

1、pg创建序列：

SELECT nextval(‘seq_bsm’ :: regclass) as XH;//查询序列值
//创建序列
CREATE SEQUENCE seq_bsm INCREMENT BY 1 MINVALUE 0 MAXVALUE 9999999999999999 START WITH 1 NO CYCLE;

2、pg uuid生成

以下为两种序列函数
create extension “uuid-ossp”
select uuid_generate_v4() as bsm;
create extension pgcrypto；
select gen_random_uuid()；
不需要可以删除uuid函数： drop extension pgcrypto

3、Postgresql使用coalesce实现类似oracle的NVL方法

COALESCE (expression_1, expression_2, …,expression_n)
依次参考各参数表达式，遇到非null值即停止并返回该值。
如果所有的表达式都是空值，最终将返回一个空值。
使用COALESCE在于大部分包含空值的表达式最终将返回空值。
SELECT coalesce(collect_result,0) as collect_result FROM collect
数据库中如果查询的字段collect_result为空那么赋值0给collect_result。

4、pg 实现递归查询：

WITH RECURSIVE dict AS (SELECT * FROM tablename WHERE 条件 union ALL SELECT copy_data_deploy.* FROM tablename , dict WHERE tablename .父字段=dict.子字段
)
SELECT * FROM dict

5、pg外联查询

数据库使用中，当一条sql语句或者一个事务中的查询将会涉及到两个数据库（同服务或者不同的服务地址），此时需要实现跨库查询。就将会使用到外联查询。

1. 使用外联需要安装扩展
   – 创建oracle_fdw
   create extension postgres_fdw;(mysql_fdw,oracle_fdw)
   – 语句能查询到外联扩展：
   select * from pg_available_extensions;
   

2）创建外联服务

create server 外联服务名 FOREIGN data wrapper postgres_fdw options(host ‘外部数据库ip’, port ‘端口’, dbname ‘自定义外联数据库名’);
示例：create server testfwd_server FOREIGN data wrapper postgres_fdw OPTIONS(host ‘172.0.0.1’, port ‘5432’, dbname ‘test1’);
查询创建的外联服务：SELECT * from pg_foreign_server;

3）创建外联服务的用户、密码

create user mapping for postgres server testfwd_server options(user ‘postgres’,password ‘postgres’);

4）导入外部数据库的模式

import foreign schema 外部数据库的模式名 from server testfwd_server into 当前数据库的模式名;

5）此时远程数据库的目标模式下的所有表均已经成功导入到当前指定的模式下，导入可以仅仅指定某张表，而不是整个模式。

mysql没有模式一说，所以关联表有所不同：CREATE FOREIGN TABLE 当前表名 SERVER mysql_server OPTIONS (dbname ‘外部数据库名’, table_name ‘外部表名’);
create foreign table 当前表 server testfwd_server options(schema ‘外部模式名’,table ‘外部表名’);

6）导入后就可以使用当前数据库中的表进行查询，与远程数据库数据一致的

6、修改空间字段坐标系

实际应用种发现坐标系不一致，导致压占分析不出结果，于是需要更改坐标系一致
SELECT updategeometrysrid(‘tablename’,‘geom’,4525);

7、清空表数据

truncate table tablename