3.postgresql数据库基本操作

--创建用户，创建空间目录，创建数据库，赋权

postgres=# create role pguser with password 'pguser';
CREATE ROLE
[root@localhost ~]# mkdir -p /database/pg10/pg_tbs/tbs_mydb
root@localhost ~]# chown -R postgres.postgres /database
[postgres@localhost ~]$ psql
psql (10.0)
Type "help" for help.

postgres=# create tablespace tbs_mydb owner pguser location '/database/pg10/pg_tbs/tbs_mydb';
CREATE TABLESPACE
postgres=# create database mydb with owner = pguser TEMPLATE=template0 ENCODING = 'UTF8' TABLESPACE=tbs_mydb;
CREATE DATABASE

postgres=# grant ALL on DATABASE mydb to pguser with Grant option;(WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人)
GRANT
postgres=# grant ALL on TABLESPACE tbs_mydb to pguser ;
GRANT

postgres=# alter role pguser login ;
ALTER ROLE

--psql元命令介绍

\l查看库

\db查看表空间

 

\d查看表定义

mydb=> create table test_1(id int,name text ,create_time timestamp without time zone default clock_timestamp());
CREATE TABLE

mydb=> alter table test_1 add primary key (id);
ALTER TABLE
mydb=> \d test_1
Table "public.test_1"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-------------------
id | integer | | not null |
name | text | | |
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"test_1_pkey" PRIMARY KEY, btree (id)

查看表，索引大小

mydb=> insert into test_1 (id,name) select n,n || '_francs' from generate_series(1,500000) n;
INSERT 0 500000
mydb=> select * from test_1  limit 1;
 id |   name   |        create_time
----+----------+----------------------------
  1 | 1_francs | 2021-10-13 03:36:25.607786
(1 row)
mydb=> \di+ test_1_pkey
                          List of relations
 Schema |    Name     | Type  | Owner  | Table  | Size  | Description
--------+-------------+-------+--------+--------+-------+-------------
 public | test_1_pkey | index | pguser | test_1 | 11 MB |
(1 row)

\sf查看函数代码

\x设置查询结果输出模式

psql导入、导出数据表

COPY是sql命令，需要superuser，主机上的文件

\copy是元命令，不需要superuser，psql端文件

COPY导入导出命令

[postgres@localhost ~]$ cat test_copy.txt
1    a
2    b
3    c
4    d
[postgres@localhost ~]$ psql mydb postgres
mydb=# COPY public.test_copy from '/home/postgres/test_copy.txt';（实例名）
COPY 4
mydb=# select * from test_copy ;
 id | name
----+------
  1 | a
  2 | b
  3 | c
  4 | d
(4 rows)

导出

mydb=# COPY public.test_copy to '/home/postgres/test.txt';
COPY 4
mydb=# \q
[postgres@localhost ~]$ ls
test_copy.txt  test.txt
[postgres@localhost ~]$ cat test.txt
1    a
2    b
3    c
4    d

导出到csv格式：加 with csv header导出格式为csv，并显示字段名称

mydb=# copy public.test_copy to '/home/postgres/test_copy.csv' with csv header;
COPY 4

 

 如何导出部分数据，导出表test_copy，id等于2的数据

mydb=# copy (select * from public.test_copy where id=2) to '/home/postgres/test_copy.csv' with csv header;
COPY 1
mydb=# \q
[postgres@localhost ~]$ ls
test_copy.csv  test_copy.txt  test.txt
[postgres@localhost ~]$ cat test_copy.csv
id,name
2,b

 

\copy原命令数据导入导出，一样的，把COPY命令换位\copy即可

小表用\copy,大表建议用COPY效率更高