PostgreSQL - 随笔分类 - program_keep

根据某张表更新另一张表字段

摘要：语法： UPDATE TABLE_A SET (field_1,field2) = (SELECT field_1,field2 FROM TABLE_B WHERE TABLE_B.id = TABLE_A.id); 示例： UPDATE sys_log SET ( org_id ) = ( SE 阅读全文

posted @ 2025-12-05 16:39 program_keep 阅读(2) 评论(0) 推荐(0)

查看pg某数据库创建时间

摘要：1.查询数据库oid SELECT oid,datname from pg_database where datname ='' postgres=# select oid,datname from pg_database where datname='pure_gtjt'; oid | datna 阅读全文

posted @ 2025-08-29 16:16 program_keep 阅读(84) 评论(0) 推荐(0)

pg快捷删除schema的SQL

摘要：1.查询符合条件的SQL: select 'drop schema '|| nspname || ' cascade;' from pg_namespace where nspname like 'schema_name_prefix%' 2.选择查询结果并执行阅读全文

posted @ 2025-07-02 16:10 program_keep 阅读(20) 评论(0) 推荐(0)

pg terminate backend

摘要：提要：postgresql-13-A4.pdf 文档末尾有Index，如果一时找不到命令在哪里，先在Index里查 1.pg_stat_activity SELECT * FROM pg_stat_activity 2.pg_terminate_backend SELECT pg_terminate 阅读全文

posted @ 2025-06-27 15:22 program_keep 阅读(8) 评论(0) 推荐(0)

Unzip compressed dump and import via psql

摘要：Use compressed pg_dump: Using -Z pg_dump dbname -Fp -Z6 -v > filename.gz Using gzip pg_dump dbname | gzip > filename.gz Reload data: gunzip gunzip -c 阅读全文

posted @ 2025-04-21 17:17 program_keep 阅读(20) 评论(0) 推荐(0)

create index

摘要：CREATE INDEX IF NOT EXISTS INDEX_NAME ON tableName (fieldName1,fieldName2...); example: CREATE INDEX IF NOT EXISTS draw_orgid_maptype_index on draw(or 阅读全文

posted @ 2025-04-19 09:52 program_keep 阅读(21) 评论(0) 推荐(0)

pg_dump plain data with specific schema and table by compressed

摘要：pg_dump -p5433 -Upostgres -d dbName -n 'public|delphi' -T 'public.excludeTableName|delphi.excludeTableName' -Fp -v -Z9 > data.gz 阅读全文

posted @ 2025-03-29 18:27 program_keep 阅读(11) 评论(0) 推荐(0)

ERROR: collation "zh_CN.utf8" for encoding "UTF8" does not exist的解决方案

摘要：增加zh_CN.utf8排序规则 CREATE COLLATION "zh_CN.utf8" (provider = libc, locale = 'zh_CN.utf8'); 阅读全文

posted @ 2025-01-20 11:15 program_keep 阅读(142) 评论(0) 推荐(0)

在线安装pg13的postgis插件

摘要：1.安装yum源 yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm 2.查看postgis所有版本 yum list 阅读全文

posted @ 2025-01-15 15:32 program_keep 阅读(98) 评论(0) 推荐(0)

随笔分类 - PostgreSQL

公告