postgres日常操作

1.启动pgsl数据库

[postgres@master ~]$ pg_ctl start
[postgres@master data]$ pg_ctl -D /usr/local/pgsql/data  start

2.查看pgsql版本

[postgres@master ~]$ pg_ctl --version

3.命令行登录数据库

psql -U username -d dbname -h hostip -p port
[postgres@master ~]$ psql -U zhang -d mydb -h 127.0.0.1  -p 5432
psql (10.5)
Type "help" for help.
mydb=>

4.列出所有数据库

[postgres@master ~]$ psql -U zhang -d mydb -h 127.0.0.1  -p 5432
psql (10.5)
Type "help" for help.
mydb=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(5 rows)

5.切换数据库

mydb=> \c test
You are now connected to database "test" as user "zhang".

6.指定表的所有字段

mydb=> \d employees
             Table "public.employees"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 age    | integer |           |          | 
mydb=> 

 7.查看指定表的基本情况

mydb=> \d+ employees
                                 Table "public.employees"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              | 
 age    | integer |           |          |         | plain   |              | 
mydb=> 

8.新建表

create table TESTCASE(
id INTEGER, 
task_class INTEGER,
age TEXT,
PRIMARY KEY(id, task_class)
);

9.自增SERIAL

create table CREATETASK_CHKID_N( 
id SERIAL PRIMARY KEY, 
chk_id TEXT, 
n INTEGER
);
#生成一个以表名_id_seq的序列
mydb=> \d createtask_chkid_n_id_seq
             Sequence "public.createtask_chkid_n_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.createtask_chkid_n.id
#查看下一个值
mydb=> select nextval('createtask_chkid_n_id_seq');
 nextval 
---------
       1
(1 row)
#插入数据
insert into CREATETASK_CHKID_N values(nextval('createtask_chkid_n_id_seq'),'zhang',10)

10.删除表

drop table CREATETASK_CHKID_N

11.清空表

truncate table  TESTCASE;
delete from  TESTCASE;

12.添加字段

alter table [表名] add column [字段名] [类型];
alter table testcase add column rate_term numeric(5,0);

13.更改字段名称

alter table [表名] rename column [旧字段名] to [新字段名];

ALTER TABLE testcase rename  column rate_term to rate;

14.修改列属性

ALTER TABLE testcase ALTER COLUMN rate TYPE integer;

15.执行SQL文件

psql -h localhost  -d databaseName  -U username -f  filename
psql -h 127.0.0.1  -d mydb -U zhang -f testcase.sql

16. 查询结果存储到输出文件

mydb=> \o /home/postgres/exp_testcase.dat
mydb=> select id from testcase;
mydb=> \q
[postgres@master ~]$ more exp_testcase.dat 
 id 
----
  1
  2
(2 rows)