postgreSql 常用操作总结
0. 启动pgsl数据库
pg_ctl -D /xx/pgdata start
1. 查看pgsl版本
pg_ctl --version
1. 命令行登录数据库
psql -U username -d dbname -h hostip -p port
2. 列出所有数据库
\l
3. 切换数据库
\c dbname
4. 列出当前数据库的所有表
\d
5. 查看指定表的所有字段
\d tablename
6. 查看指定表的基本情况
\d+ tablename
7. 退出操作
q
8. 新建表
例1（主键）
create table TESTCASE(
id INTEGER,
task_class INTEGER,
age TEXT,
PRIMARY KEY(id, task_class)
);
例2（自增SERIAL）
create table CREATETASK_CHKID_N(
id SERIAL PRIMARY KEY,
chk_id TEXT,
n INTEGER
);
其中SERIAL代表自增，默认从1开始增加，每次自增1。
9. 删除表
drop table REL_CROSS_NODE;
10. 清空表
delete from [表名]
or
TRUNCATE TABLE [表名]
区别：Truncate table 表名 (注:不带where语句) 速度快,而且效率高。
因为DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
11. 添加字段
alter table [表名] add column [字段名] [类型];
12. 更改字段
alter table [表名] rename column [旧字段名] to [新字段名];
例：把表table_ex字段col_1限制非空去掉：ALTER TABLE table_eg ALTER col_1 drop not NULL
12.1 更改字段属性，含空格
如果把字段colname把属性Text转化为int，原来text里面存在空啥的，可以
ALTER TABLE tablename ALTER COLUMN colname TYPE int USING (trim(colname)::integer);
12.2 更改字段由int4-->int8
alter table test_data alter column task_id type bigint using task_id::bigint
12.3 更新默认值
alter table alf_authority alter column visible set default '1';
13. 删除字段
alter table [表名] drop column [字段名];
14. 表中插入一行数据
insert into [表名] (字段1,字段2) values (值1,值2);
例如：
insert into assist_info (id, maat_id, block_type) values ('F006', 'F7775', 1)
注：
- 如果表中字段有大写的字段，则需要对应的加上双引号。例：insert into test (no, "Name") values ('123', 'jihite');
- 值用单引号引起来('')，不能用双引号（""）
15. 表中删除一行数据
delete from [表名] where [该行特征];
16. 修改表中数据
update [表名] set [目标字段名]=[目标值] where [该行特征]
17. 删除表
drop table [表名];
18. 退出postgreSql
\q
19. 两个查询结果做差 except
(select node_id from node where node_id=1 or node_id=2) except (select node_id from node where node_id=1);
node_id
---------
2
(1 row)
20. 复制表
CREATE TABLE test_a_copy AS SELECT * FROM test_a;
21.命令导入sql数据文件
psql -h localhost -d databaseName -U username -f filename
22. 查询结果存储到输出文件
格式：
\o file_path
这样就会把查询结果存储到输出文件中。例
postgres=> \o /home/jihite/data/iu_data; postgres=> select test_id from cdb_all_iu_data limit 10; postgres=> select test_id from cdb_all_iu_data limit 5;
结果
test_id -------------- 2143 2153 2144 2156 2145 2154 2146 2157 2147 2155 (10 rows) test_id -------------- 2143 2153 2144 2156 2145 (5 rows)
23. 数据库的备份&恢复
导出到线下文件
pg_dump --host hostname --port port --username username -t tablename -d dbname >/home/jihite/table.sql
把线下文件导入到数据库
psql -h 10.125.7.68 -p 5432 -d postgres -U postgres -W postgres -f 2.sql
只导出建表语句
pg_dump --host=host --port=port --username=username --table=tablename --dbname=dbname -s >out.sql
24. \x
postgres=> \x Expanded display is on. postgres=> select * from cdb_chk_items where chk_id = 'R000000335'; -[ RECORD 1 ]+------------------------------------------------------------------------------------------------ chk_id | R000000335 chk_desc | 道路属性与道路属性相关检查 chk_info | {"FIELDS": {"TRAFFIC_SIGN": ["TYPE", "GEOM"], "ROAD_LINK": ["ROAD_CLASS", "FORM_WAY", "GEOM"]}} err_desc | {"ERR2": "roadclass取值错误", "ERR1": "formway取值错误"} chk_level | 1 is_opened | 1 module_name | TRAFFIC_SIGN invalid_flag | 1 rel_mode | MAIN_LAYER:TRAFFIC_SIGN : TRAFFIC_SIGN|A,M|DIRECT : ROAD_LINK|A,M,D|ATTR_REL
25. 从表A中把符合条件的记录拷贝到表B
insert into A select * from B where id in ('a', 'b', 'c');
26 建立索引
单字段索引
CREATE INDEX index_name ON table_name (field1);
多字段索引
CREATE INDEX index_name ON table_name (field1,field2);
查看所有表的索引使用情况
select
relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from
pg_stat_user_indexes
order by
idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
查看某个表索引的使用情况
select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where relname = table_name order by idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
27. 超找数据库的连接信息
select * from pg_stat_activity
包含：客户端user、ip、执行语句，状态、时间
28. 查看函数
在postgresql数据库中想查询自定义函数代码则可以在客户端使用\sf func_name, 也可以使用sql语句：select prosrc from pg_proc where proname=’func_name’
29. 通过文件导入
psql **** -f ind_statis3.sql
30.修改自增ID
Select nextval('id_seq'); CREATE SEQUENCE topo_id_seq START 2000; ALTER TABLE "R_LANEINFO_TOPO" ALTER COLUMN "ID" SET DEFAULT nextval('topo_id_seq'::regclass);
duplicate key value violates unique constraint 解决方案
https://blog.csdn.net/weixin_44663044/article/details/120033749
31.COPY 导出
psql "host=${dbhost} port=${dbport} user=${dbuser} dbname=${dbname} sslmode=disable" -c "\COPY (select * from ${src_table} where ${tile_where_con} ) TO '${path}/tile_${src_table}.csv'"
32 COPY 导入
psql "host=${dbhost} port=${dbport} user=${dbuser} dbname=${dbname} sslmode=disable" -c "\COPY ${src_table} FROM ${path}/${src_table}.csv"
33 重命名表
alter table t_table_old rename to t_table_new;
34 触发器
-- 查看当前数据库所有的触发器
SELECT * FROM pg_trigger where tgname like '%t_geom_prepared%'
--列举出特定表的触发器
SELECT * FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='t_geom_prepared';
--删除特定表的触发器
DROP TRIGGER trg_a ON t_distribute_dispatch a;
35. 时间戳转换字符串
to_char(insert_time,'YYYY-MM-DD')
字符串转时间戳
to_timestamp(insert_time, 'YYYY-MM-DD HH24:MI:SS')
35.1 字符串提取秒
select extract(epoch from '03:21:06.678'::time), ceil(extract(epoch from '03:21:06.678'::time));
36.to_jsonb
SELECT to_jsonb(20) AS "to_jsonb(20)", to_jsonb('x'::text) AS "to_jsonb('x'::text)", to_jsonb(array[[1, 2], [3, 4]]) AS "to_jsonb(array[[1, 2], [3, 4]])", to_jsonb(true) AS "to_jsonb(true)", to_jsonb(false) AS "to_jsonb(false)";
37.json转换
(payload->>'track_length')::DECIMAL)
select * from public.t_intelligence_basic where (payload->>'main_info')::jsonb->>'one_key'='556946146333'
修改json 字段
UPDATE t_task_abc SET payload = jsonb_set(payload, '{bucket_id}', '""') where production_line_code='P_ABC';
参考：修改json内字段
https://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4
38.空间函数
st_astext: 返回表示几何的文本字符串， 如POLYGON
st_union: 区域合并
st_intersects：判断区域是否相交
ST_GeomfromText： 从字符串转化为geom 格式
合并
SELECT ST_AsText(st_union( ST_GeomFromText('POLYGON()'), ST_GeomFromText('POLYGON()') ) );
39.类型转换
select CAST (payload->>'track_length' AS DOUBLE precision ) from t_table where 1=1
SELECT CAST ('10.2' AS DOUBLE precision);
40. 查看字段类型
pg_typeof()
41. 数组操作
| Operator | Description | Example | Result |
|---|---|---|---|
| = | equal | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] | t |
| <> | not equal | ARRAY[1,2,3] <> ARRAY[1,2,4] | t |
| < | less than | ARRAY[1,2,3] < ARRAY[1,2,4] | t |
| > | greater than | ARRAY[1,4,3] > ARRAY[1,2,4] | t |
| <= | less than or equal | ARRAY[1,2,3] <= ARRAY[1,2,3] | t |
| >= | greater than or equal | ARRAY[1,4,3] >= ARRAY[1,4,3] | t |
| @> | contains | ARRAY[1,4,3] @> ARRAY[3,1,3] | t |
| <@ | is contained by | ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] | t |
| && | overlap (have elements in common) | ARRAY[1,4,3] && ARRAY[2,1] | t |
| || | array-to-array concatenation | ARRAY[1,2,3] || ARRAY[4,5,6] | {1,2,3,4,5,6} |
| || | array-to-array concatenation | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] | {{1,2,3},{4,5,6},{7,8,9}} |
| || | element-to-array concatenation | 3 || ARRAY[4,5,6] | {3,4,5,6} |
| || | array-to-element concatenation | ARRAY[4,5,6] || 7 | {4,5,6,7} |
42 查询多个字段
select * from starmerx_user_permission where (user_name,location_id,permission_type) not in (('xxx',315,'internal_in_sign'))
43 表大小
-- 查询单个表的总大小，包括该表的索引大小 select pg_size_pretty(pg_total_relation_size('tab')) as size; -- 查询所有表空间大小 select spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace; -- 查询所有表的总大小，包括其索引大小 select relname, pg_size_pretty(pg_total_relation_size(relid)) as size from pg_stat_user_tables;
SELECT relname AS "table_name", pg_size_pretty(pg_table_size(C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') ORDER BY pg_table_size(C.oid) DESC LIMIT 20;
44 创建只读账号
CREATE USER readonly_user WITH PASSWORD 'password'; GRANT CONNECT ON DATABASE db1 TO readonly_user; GRANT USAGE ON SCHEMA public TO readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
