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
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 建⽴索引
postgre trunc函数的使用方法
单字段索引
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解决⽅案

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。