pgSQL笔记

最新推荐文章于 2024-05-13 14:03:56 发布

Risey易木

最新推荐文章于 2024-05-13 14:03:56 发布

阅读量358

点赞数

文章标签：笔记 postgresql

本文链接：https://blog.csdn.net/weixin_43037331/article/details/130502461

版权

pgSQL笔记

数据类型

数值类型

名字	存储长度	描述	范围
smallint	2字节	小范围整数	-32768到+32767
integer	4字节	常用的整数	-2147483648 到 +2147483647
bigint	8字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4字节	可变精度，不精确	6 位十进制数字精度
double precision	8字节	可变精度，不精确	15 位十进制数字精度
smallserial	2字节	自增的小范围整数	1到32767
serial	4字节	自增整数	1 到 2147483647
bigserial	8字节	自增的大范围整数	1 到 9223372036854775807

货币类型

money 类型存储带有固定小数精度的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。

名字	存储长度	描述	范围
money	8字节	货币金额	-92233720368547758.08 到 +92233720368547758.07

字符类型

序号	名字&描述
1	character varying(n), varchar(n) 变长，有长度限制
2	character(n), char(n) 定长，不足补空白
3	text 变长，无长度限制

枚举类型

postgres=# CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
CREATE TYPE
postgres=# create table temp_date(f_date varchar(16), date_type week);
CREATE TABLE
postgres=# insert into temp_date values('2021-02-25', 'Thu');
INSERT 0 1
postgres=# insert into temp_date values('2021-02-24', 'wedo');
错误:  对于枚举week的输入值无效: "wedo"
第1行insert into temp_date values('2021-02-24', 'wedo');
                                                ^
postgres=# 
postgres=# select * from temp_date;
   f_date   | date_type 
------------+-----------
 2021-02-25 | Thu
(1 行记录)

PostgreSQL命令

abort

用于退出当前事务

abort [ work | transcation ]

数组类型

postgres=# CREATE TABLE sal_emp (
postgres(#    name text,
postgres(#    pay_by_quarter integer ARRAY[4],
postgres(#    schedule text[][]
postgres(# );
CREATE TABLE
postgres=# INSERT INTO sal_emp
postgres-#     VALUES ('Bill',
postgres(#     '{10000, 10000, 10000, 10000}',
postgres(#     '{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT 0 1
postgres=# INSERT INTO sal_emp
postgres-#     VALUES ('Carol',
postgres(#     '{20000, 25000, 25000, 25000}',
postgres(#     '{{"breakfast", "consulting"}, {"meeting", "lunch", "aaaatttt"}}');
错误:  有缺陷的数组常量:"{{"breakfast", "consulting"}, {"meeting", "lunch", "aaaatttt"}}"
第4行    '{{"breakfast", "consulting"}, {"meeting", "lunch", "aaa...
         ^
描述:  多维数组必须有具有匹配维度的子数组
postgres=# INSERT INTO sal_emp
postgres-#     VALUES ('Carol',
postgres(#     '{20000, 25000, 25000, 25000}',
postgres(#     '{{"breakfast", "consulting", "yyyyhhh"}, {"meeting", "lunch", "aaattt"}}');
INSERT 0 1
postgres=# select * from sal_emp;
 name  |      pay_by_quarter       |                        schedule                         
-------+---------------------------+---------------------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting,yyyyhhh},{meeting,lunch,aaattt}}
(2 行记录)

查询数组：
postgres=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
 name  
-------
 Carol
(1 行记录)

更新数组：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

数组的检索：
要搜索一个数组中的数值，你必须检查该数组的每一个值。

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;
 
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

postgres=# SELECT pay_by_quarter,
postgres-#            generate_subscripts(pay_by_quarter, 1) AS s
postgres-#       FROM sal_emp;
      pay_by_quarter       | s 
---------------------------+---
 {10000,10000,10000,10000} | 1
 {10000,10000,10000,10000} | 2
 {10000,10000,10000,10000} | 3
 {10000,10000,10000,10000} | 4
 {20000,25000,25000,25000} | 1
 {20000,25000,25000,25000} | 2
 {20000,25000,25000,25000} | 3
 {20000,25000,25000,25000} | 4
(8 行记录)

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

复合类型

创建：
CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

插入：
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
INSERT INTO on_hand VALUES ('("test", 42, 1.99)', 1000);
postgres=# select * from on_hand;
          item          | count 
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
 (test,42,1.99)         |  1000
(2 行记录)

查询：
postgres=# SELECT item.name FROM on_hand WHERE item.price > 9.99;
错误:  对于表"item",丢失FROM子句项
第1行SELECT item.name FROM on_hand WHERE item.price > 9.99;
            ^
postgres=# SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
 name 
------
(0 行记录)
或者如果你也需要使用表名字(比如，在一个多表查询里)，那么这么写：SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

建表语句

CREATE TABLE public.ls_xuanwu_qinhuai_road_free_1
(
    gid integer NOT NULL,
    osm_id character varying(10) ,
    code smallint,
    fclass character varying(28) ,
    name character varying(100) ,
    ref character varying(20) ,
    oneway character varying(1) ,
    maxspeed smallint,
    layer double precision,
    bridge character varying(1) ,
    tunnel character varying(1) ,
    geom geometry(MultiLineString,4326),
    CONSTRAINT ls_xuanwu_qinhuai_road_free_1_pkey PRIMARY KEY (gid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.ls_xuanwu_qinhuai_road_free_1
    OWNER to postgres;

查询某个区域的路网

select * from
      (select a.osm_id, a.code, a.name, a.layer,a.bridge, a.tunnel, a.geom as am, b.geom as bm  from gis_osm_roads_free_1 a,
      (select geom from bt_boundary_info where gid = 3541) b
) c where ST_Contains(bm, am) and code between 5112 and 5116 and (bridge = 'F' AND tunnel = 'F') AND layer <> 0;

select a.geom, b.geom
from （select geom from bt_boundary_info where gid = 3541) a ,
(select ST_PolygonFromText('POLYGON((120.7994006 31.1090894,120.7994006 31.18152913333333,120.88871225 31.18152913333333,120.88871225 31.1090894,120.7994006 31.1090894))') as geom) b

select ST_Distance(d.am, e.point)
from 
(select * from
      (select a.osm_id, a.code, a.name, a.layer,a.bridge, a.tunnel, a.geom as am, b.geom as bm  from gis_osm_roads_free_1 a,
      (select geom from bt_boundary_info where gid = 3541) b
) c where ST_Contains(bm, am) and code between 5111 and 5116) d,
(select ST_GeomFromText('POINT(120.91352463801475 31.38317547193085)',4326) as point) e
limit 10;

select * from
      (select a.gid, a.code, a.name, a.layer,a.bridge, a.tunnel, a.geom as am, bm  from ls_xuanwu_qinhuai_road_free_1 a,
      (select ST_GeomFromText('POLYGON((118.7913875 32.0527149,118.7875045 32.0365787,118.8255152 32.0266252,118.8284512 32.0491844,118.7913875 32.0527149))',4326) as bm) b
) c where ST_Contains(bm, am) ;

select *
from (
select f.am, f.point, f.distance, row_number() over(partition by point order by distance desc) rank
from(
select d.am, e.point, ST_Distance(d.am, e.point) as distance
from 
(select * from
      (select a.osm_id, a.code, a.name, a.layer,a.bridge, a.tunnel, a.geom as am, b.geom as bm  from gis_osm_roads_free_1 a,
      (select geom from bt_boundary_info where gid = 3541) b
) c where ST_Contains(bm, am) and code between 5111 and 5116) d,
(select geom as point from sz_kunshan_outlets) e
	) f
)e
where rank < 3
limit 10;


select * from bt_boundary_info where properties -> 'all_tags' ->> 'name:zh' like '%丰台%';

插入语句

insert into ls_xuanwu_qinhuai_road_free_1 values
(1568878,'14348988',5114,'secondary','洪武北路',NULL,'B',0,0,'F','F',ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))',4326))

insert into public.ls_xuanwu_qinhuai_road_free_1 
select gid,
       osm_id,
	   code,
	   fclass,
	   name,
	   ref,
	   oneway,
	   maxspeed,
	   layer,
	   bridge,
	   tunnel,
	   am as geom
	   from
      (select a.gid,
	          a.osm_id, 
	          a.code, 
	          a.fclass,
	          a.name, 
	          a.ref,
	          a.oneway,
	          a.maxspeed,
	          a.layer,
	          a.bridge, 
	          a.tunnel, 
	          a.geom as am, 
	          b.geom as bm  from gis_osm_roads_free_1 a,
      (select geom from bt_boundary_info where gid  in(885, 879)) b
) c where ST_Contains(bm, am) and (code between 5111 and 5126 or code = 5133);

insert into sz_kunshan_outlets values
('工行巴城支行','昆山市巴城镇新澄路908号',ST_GeomFromText('POINT(120.87543250346141 31.458303887974083)',4326)),
('工行柏芦支行','昆山市玉山镇柏庐南路1061号',ST_GeomFromText('POINT(120.96268313779113 31.368791482681864)',4326))

psql

\l 查看数据库
\c databaseName 使用数据库
\d 查看所有表
查看某个库中的某个表结构：\d 表名
\d tablename 查看表格信息

createdb

创建数据库

在Linux命令行输入：createdb mydb
psql mydb

dropdb

在物理上把所有与该数据库相关的文件都删除并且不可取消

dropdb mydb

alter

alter aggregate

修改一个聚合函数的定义

ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) RENAME TO _new_name_
ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) OWNER TO _new_owner_
ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) SET SCHEMA _new_schema_

current_date

获取当前日期

SELECT current_date;
2021-03-05

ST_Distance

结果不正确：
select ST_Distance(
	ST_GeomFromText('POINT(121.0814039 31.4315026)',4326),
    ST_GeomFromText('POINT(121.086792545792 31.4489357546911)',4326)
);

结果正确，单位：M
select ST_Distance('MULTILINESTRING((121.0824414 31.4278993,121.0824105 31.4281903,121.0823327 31.428561,121.0822066 31.4290439,121.0817748 31.4303737,121.0814039 31.4315026))'::geography, 'POINT(121.086792545792 31.4489357546911)'::geography)

select gid, name, ST_AsText(am) as am, address, ST_AsText(point) as point, distance
from
    ( select
    f.gid, f.name, f.am, f.address,f.point, f.distance,
    row_number() over(partition by point order by distance) rank
    from
         ( select d.gid, d.name, d.am, e.address, e.point, ST_Distance(d.am::geography, e.point::geography) as distance
         from
             (select *
             from
                 (select a.gid,
                         a.osm_id,
                         a.code,
                         a.name,
                         a.layer,
                         a.bridge,
                         a.tunnel,
                         a.geom as am,
                         b.geom as bm
                         from gis_osm_roads_free_1 a,
                         (select geom from bt_boundary_info where gid = 3541) b
                   ) c
              where ST_Contains(bm, am) and code between 5111 and 5116) d,
              (select address,geom as point from sz_kunshan_outlets) e
          ) f
    )e
where rank < 3
order by address,distance desc

数据导入导出

pg_dump -h ip -U postgres -t bt_boundary_info location > boundary.sql

psql -h ip -U user 数据库名(缺省时同用户名) < boundary.sql

报错

ST_Intersects: Operation on mixed SRID geometries (Polygon, 4490) != (MultiPolygon, 0)


先查看一下SRID信息：
select st_srid(smgeometry) from jc_jzwa;
更新语句为
select UpdateGeometrySRID('jc_jzwa', 'smgeometry', 4490);

脚本导入shp文件


PGPASSWORD=postgres

DATAFILES=$(ls *.shp)
for datafile in $DATAFILES; do
    shp2pgsql -c -W "GBK" -s 4326 -I $datafile > ${datafile}.sql
    PGPASSWORD=postgres psql -h ip -U postgres -d location -f ${datafile}.sql
done


CREATE TABLE gis_osm_boundary AS
select gid,
abs(cast(properties ->> 'id' as integer)) as boundary_id,
replace(cast(properties ->> 'parents' as varchar(100)), '-', '') as parents,
cast(properties ->> 'name' as varchar(100)) as name,
cast(properties ->> 'local_name' as varchar(100)) as local_name,
cast(properties ->> 'admin_level' as integer) as admin_level,
geom
from bt_boundary_info;