PostgreSQL 日常SQL语句查询记录

记录开发过程中使用的各种SQL语句！

创建扩展

CREATE EXTENSION POSTGIS;
CREATE EXTENSION POSTGIS_RASTER;

查询扩展

SELECT name, default_version, installed_version
FROM pg_available_extensions;

查询具体某个函数

-- 查询具体的函数 例如：st_fromgdalraster
SELECT proname, proargtypes, prosrc
FROM pg_proc
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE proname = 'st_fromgdalraster';-- 或者是
SELECT proname, proargtypes
FROM pg_proc
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE proname LIKE 'st_f%' AND pg_namespace.nspname = 'public';

查看表字段的注释

-- tabName 表名SELECTb.column_name AS 字段名,b.data_type AS 类型,b.character_maximum_length AS 长度,a.attnotnull AS 非空,col_description(a.attrelid, a.attnum) AS 注释FROMinformation_schema.columns AS bINNER JOIN pg_class AS c ON b.table_name = c.relnameINNER JOIN pg_attribute AS a ON a.attrelid = c.oid and a.attnum = b.ordinal_positionWHEREc.relname = '${tabName}'

或者是查询固定多少个字段的注释

-- 例如查询非第一列之后的字段
SELECT a.attname                             as 字段名,col_description(a.attrelid, a.attnum) as 注释FROM pg_class as c,pg_attribute as awhere a.attrelid = c.oidand a.attnum > 1and c.relname = '${tabName}'

查询这个直线

-- 查询这条直线
SELECT ST_AsText(geom) FROM sdx.testline;

-- 创建表
CREATE TABLE IF NOT EXISTS sdx.linedata (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name varchar(255), geom geometry)
-- 或是
CREATE TABLE IF NOT EXISTS sdx.linedata (id SERIAL PRIMARY KEY, name varchar(255), geom geometry)-- 插入一条直线，直线由两个点定义
INSERT INTO sdx.linedata (name,geom) VALUES ('xian',ST_GeomFromText('MULTILINESTRING((108.12988184124956 34.4826445579113,108.6313532482283 34.621843026752366,108.64919920577202 34.65039655882233,108.6509838015264 34.71821119748849,108.64741461001765 34.73784175078659))',4326)
);INSERT INTO sdx.linedata (name, geom) 
VALUES ('example_line',ST_SetSRID(ST_GeomFromText('LINESTRING(108.12988184124956 34.4826445579113, 108.13988184124956 34.4926445579113)'), 4326)
);-- 点数据
INSERT INTO sdx.pointdata (name, geom) 
VALUES ('xiandian',ST_SetSRID(ST_MakePoint(108.12988184124956, 34.4826445579113), 4326)
);-- 面数据
INSERT INTO sdx.polygondata (name, geom) 
VALUES ('example_polygon',ST_SetSRID(ST_GeomFromText('POLYGON((108.12988184124956 34.4826445579113, 108.13988184124956 34.4826445579113, 108.13988184124956 34.4926445579113, 108.12988184124956 34.4926445579113, 108.12988184124956 34.4826445579113))'), 4326)
);-- ST_MakePoint(x, y)：用于创建一个点对象，x 是经度，y 是纬度。
-- ST_SetSRID(geometry, srid)：设置空间参考系统的 ID。

 空间索引：为了提高空间查询的性能，建议在 geom 列上创建一个空间索引：

-- GIST（Generalized Search Tree）是处理空间数据的索引类型。
CREATE INDEX idx_geom ON sdx.pointdata USING GIST (geom);

-- 获取几何数据的SRID
SELECT ST_SRID(geom) FROM sdx.linedata LIMIT 1;

-- 查看三维线
SELECT ST_AsText(ST_MakeLine(ST_MakePoint(x, y, elevation))) AS wkt from sdx.tdm1_dem_profile

-- 查询插值点
WITH line AS (select geom from sdx.linedata
)
SELECTST_LineInterpolatePoint(ST_LineMerge(geom),generate_series(0, 1, 0.001)) AS point_geom
FROM line;

-- 查询该点的海拔
WITH point_geoms AS(
SELECT ST_GeomFromText('POINT(108.13606692491066 34.48436141387417)',4326) as point_geom
)
SELECTpoint_geom,ST_Value(rast, ST_Transform(point_geom, ST_SRID(rast))) AS elevation
FROM sdx.tdm1_dem, point_geoms
WHERE ST_Intersects(rast, ST_Transform(point_geom, ST_SRID(rast)))

-- 查询海拔
WITH line AS (select geom from sdx.linedata
),
sample_points AS (SELECTST_LineInterpolatePoint(ST_LineMerge(geom),generate_series(0, 1, 0.01)) AS point_geomFROM line
),
elevations AS (SELECTpoint_geom,ST_Value(rast, ST_Transform(point_geom, ST_SRID(rast))) AS elevationFROM sdx.tdm1_dem, sample_pointsWHERE ST_Intersects(rast, ST_Transform(point_geom, ST_SRID(rast)))
)
SELECTST_AsText(point_geom) AS geom,elevation
FROM elevations
WHERE elevation IS NOT NULL;-- 或是
WITH line AS (SELECT ST_GeomFromText('MULTILINESTRING((108.12988184124956 34.4826445579113,108.6313532482283 34.621843026752366,108.64919920577202 34.65039655882233,108.6509838015264 34.71821119748849,108.64741461001765 34.73784175078659))', 4326) AS geom -- 确保SRID与栅格一致
),
sample_points AS (SELECTST_LineInterpolatePoint(ST_LineMerge(geom),generate_series(0, 1, 0.01)) AS point_geomFROM line
),
elevations AS (SELECTpoint_geom,ST_Value(rast, ST_Transform(point_geom, ST_SRID(rast))) AS elevationFROM sdx.tdm1_dem, sample_pointsWHERE ST_Intersects(rast, ST_Transform(point_geom, ST_SRID(rast)))
)
SELECTpoint_geom AS geom,--ST_AsText (point_geom) AS geom,ST_X(point_geom) AS x,ST_Y(point_geom) AS y,elevation
FROM elevations
WHERE elevation IS NOT NULL;

-- 查询出一条直线
SELECT ST_MakeLine(geom) AS line
FROM (SELECT geomFROM sdx.tdm1_dem_profileORDER BY id ASC
) AS ordered_points;-- 查询出一条直线
SELECT ST_AsEWKB(ST_MakeLine(geom)) AS line
FROM (SELECT geomFROM sdx.tdm1_dem_profileORDER BY id ASC
) AS ordered_points;

-- 类型转换
SELECT CAST(str_column AS INTEGER) FROM your_table;
-- 或者使用类型转换操作符
SELECT str_column::INTEGER FROM your_table;

-- 统计
WITH gender_count AS (SELECTSUM(CASE WHEN xb = '男' THEN 1 ELSE 0 END) AS male_count,SUM(CASE WHEN xb = '女' THEN 1 ELSE 0 END) AS female_countFROM tyl_fcfhWHERE nl ~ '^[0-9]+$' AND xb IN ('男', '女') AND xqbh='1' AND ldhh LIKE '1-1%'
)
SELECTmale_count || ':' || female_count AS gender_ratio
FROM gender_count;

-- 首先，添加新字段
ALTER TABLE tyl_poi ADD COLUMN tid INTEGER;
-- 然后，为新字段添加注释
COMMENT ON COLUMN tyl_poi.tid IS '表主键';
-- 删除，表字段
ALTER TABLE tyl_poi DROP COLUMN tid;-- 1. 添加自增ID字段（假设使用SERIAL类型，它会自动创建一个序列并为主键列提供值）
ALTER TABLE my_table ADD COLUMN id SERIAL PRIMARY KEY;-- 或者，如果你想要先添加字段，然后再设置为主键：
-- 2.1 添加字段（不设置为主键）
ALTER TABLE my_table ADD COLUMN id SERIAL;-- 2.2 将字段设置为主键
ALTER TABLE my_table ADD PRIMARY KEY (id);-- 使用GENERATED ALWAYS AS IDENTITY添加自增ID字段作为主键
ALTER TABLE tyl_poi ADD COLUMN tid INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY;
COMMENT ON COLUMN tyl_poi.tid IS '表主键';

-- 首先，添加新字段
ALTER TABLE tyl_poi ADD COLUMN area character varying(255);
-- 然后，为新字段添加注释
COMMENT ON COLUMN tyl_poi.area IS '面积';
-- 最后，更新新字段的数据
UPDATE tyl_poi AS B
SET area = A.areaFROM tyl_dmdzp AS A
WHERE A.entity_nam = B.nameAND B.area IS NULL;