参考文献: Regina Obe Leo Hsu 著, 丁奇鹏 译《PostgreSQL即学即用》
使用普通用户 haife 登录, 假设数据库中已经存在 mydb 数据库且用户 haife 可以访问.
psql -U haife -d mydb
创建数据库 testbar
mydb=> CREATE DATABASE testbar; CREATE DATABASE
连接到testbar数据库
mydb=> \c testbar 您现在已经连接到数据库 "testbar",用户 "haife". testbar=>
创建表 Beers
CREATE TABLE Beers ( name VARCHAR(20) UNIQUE, manf VARCHAR(50) );
testbar=> CREATE TABLE Beers ( testbar(> name VARCHAR(20) UNIQUE, testbar(> manf VARCHAR(50) testbar(> ); CREATE TABLE
查看已建立的表
testbar=> \dt+
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 持续的 | 访问方法 | 大小 | 描述
----------+-------+--------+--------+--------+----------+---------+------
public | beers | 数据表 | haife | 永久的 | heap | 0 bytes |
(1 行记录)
依次创建下面的表.
CREATE TABLE Bars ( name VARCHAR(20) PRIMARY KEY, addr VARCHAR(255), license VARCHAR(255) ); CREATE TABLE Drinkers ( name VARCHAR(20) PRIMARY KEY, addr VARCHAR(255), phone VARCHAR(15) ); CREATE TABLE Likes ( drinker VARCHAR(20), beer VARCHAR(255), PRIMARY KEY (drinker,beer) ); CREATE TABLE Sells ( bar VARCHAR(20), beer VARCHAR(20), price REAL, PRIMARY KEY (bar, beer) ); CREATE TABLE Frequents ( drinker CHAR(20), bar VARCHAR(255), PRIMARY KEY (drinker,bar) );
testbar=> \dt+
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 持续的 | 访问方法 | 大小 | 描述
----------+-----------+--------+--------+--------+----------+------------+------
public | bars | 数据表 | haife | 永久的 | heap | 8192 bytes |
public | beers | 数据表 | haife | 永久的 | heap | 0 bytes |
public | drinkers | 数据表 | haife | 永久的 | heap | 0 bytes |
public | frequents | 数据表 | haife | 永久的 | heap | 0 bytes |
public | likes | 数据表 | haife | 永久的 | heap | 0 bytes |
public | sells | 数据表 | haife | 永久的 | heap | 0 bytes |
(6 行记录)
在建表的时候有可能会出现“对模式 public 权限不够”的错误, 例如
new_db=> CREATE TABLE Beers (
new_db(> name VARCHAR(20) UNIQUE,
new_db(> manf VARCHAR(50)
new_db(> );
错误: 对模式 public 权限不够
第1行CREATE TABLE Beers (
^
使用下面的命令查看权限表的信息.
SELECT * FROM information_schema.table_privileges WHERE table_schema = 'public';
new_db=> SELECT * FROM information_schema.table_privileges WHERE table_schema = 'public'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+---------+---------------+--------------+------------------+----------------+--------------+---------------- haife | haife | new_db | public | finitesumformula | INSERT | YES | NO haife | haife | new_db | public | finitesumformula | SELECT | YES | YES haife | haife | new_db | public | finitesumformula | UPDATE | YES | NO haife | haife | new_db | public | finitesumformula | DELETE | YES | NO haife | haife | new_db | public | finitesumformula | TRUNCATE | YES | NO haife | haife | new_db | public | finitesumformula | REFERENCES | YES | NO haife | haife | new_db | public | finitesumformula | TRIGGER | YES | NO haife | haife | new_db | public | count_to_9 | INSERT | YES | NO haife | haife | new_db | public | count_to_9 | SELECT | YES | YES haife | haife | new_db | public | count_to_9 | UPDATE | YES | NO haife | haife | new_db | public | count_to_9 | DELETE | YES | NO haife | haife | new_db | public | count_to_9 | TRUNCATE | YES | NO haife | haife | new_db | public | count_to_9 | REFERENCES | YES | NO haife | haife | new_db | public | count_to_9 | TRIGGER | YES | NO (14 行记录)
如果顺利, 则已经将上面的六张表创建好. 也可以查看一下用户关于这些表的权限.
testbar=> SELECT * FROM information_schema.table_privileges WHERE table_schema = 'public'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+---------+---------------+--------------+------------+----------------+--------------+---------------- haife | haife | testbar | public | beers | INSERT | YES | NO haife | haife | testbar | public | beers | SELECT | YES | YES haife | haife | testbar | public | beers | UPDATE | YES | NO haife | haife | testbar | public | beers | DELETE | YES | NO haife | haife | testbar | public | beers | TRUNCATE | YES | NO haife | haife | testbar | public | beers | REFERENCES | YES | NO haife | haife | testbar | public | beers | TRIGGER | YES | NO haife | haife | testbar | public | bars | INSERT | YES | NO haife | haife | testbar | public | bars | SELECT | YES | YES haife | haife | testbar | public | bars | UPDATE | YES | NO haife | haife | testbar | public | bars | DELETE | YES | NO haife | haife | testbar | public | bars | TRUNCATE | YES | NO haife | haife | testbar | public | bars | REFERENCES | YES | NO haife | haife | testbar | public | bars | TRIGGER | YES | NO haife | haife | testbar | public | drinkers | INSERT | YES | NO haife | haife | testbar | public | drinkers | SELECT | YES | YES haife | haife | testbar | public | drinkers | UPDATE | YES | NO haife | haife | testbar | public | drinkers | DELETE | YES | NO haife | haife | testbar | public | drinkers | TRUNCATE | YES | NO haife | haife | testbar | public | drinkers | REFERENCES | YES | NO haife | haife | testbar | public | drinkers | TRIGGER | YES | NO haife | haife | testbar | public | likes | INSERT | YES | NO haife | haife | testbar | public | likes | SELECT | YES | YES haife | haife | testbar | public | likes | UPDATE | YES | NO haife | haife | testbar | public | likes | DELETE | YES | NO haife | haife | testbar | public | likes | TRUNCATE | YES | NO haife | haife | testbar | public | likes | REFERENCES | YES | NO haife | haife | testbar | public | likes | TRIGGER | YES | NO haife | haife | testbar | public | sells | INSERT | YES | NO haife | haife | testbar | public | sells | SELECT | YES | YES haife | haife | testbar | public | sells | UPDATE | YES | NO haife | haife | testbar | public | sells | DELETE | YES | NO haife | haife | testbar | public | sells | TRUNCATE | YES | NO haife | haife | testbar | public | sells | REFERENCES | YES | NO haife | haife | testbar | public | sells | TRIGGER | YES | NO haife | haife | testbar | public | frequents | INSERT | YES | NO haife | haife | testbar | public | frequents | SELECT | YES | YES haife | haife | testbar | public | frequents | UPDATE | YES | NO haife | haife | testbar | public | frequents | DELETE | YES | NO haife | haife | testbar | public | frequents | TRUNCATE | YES | NO haife | haife | testbar | public | frequents | REFERENCES | YES | NO haife | haife | testbar | public | frequents | TRIGGER | YES | NO (42 行记录)
使用 psql 命令将 insert_data_GBK.sql 中的数据导入到数据库 testbar 中. 在导入过程中, 可能因编码的问题出现错误. 作为示例, 将 insert_data_GBK.sql 另存为 insert_data_UTF8.sql, 即以 UTF8 编码保存. 然后再导入.
PS D:\work\cs\Database\postgres\testbar> psql -U haife -d testbar -f insert_data_UTF8.sql INSERT 0 1 INSERT 0 1 psql:insert_data.sql:3: 错误: 编码"GBK"的字符0x0x97 0x28在编码"UTF8"没有相对应值 psql:insert_data.sql:4: 错误: 编码"GBK"的字符0x0xaf 0x27在编码"UTF8"没有相对应值 psql:insert_data.sql:8: 错误: 编码"GBK"的字符0x0xab 0x27在编码"UTF8"没有相对应值 INSERT 0 1 psql:insert_data.sql:10: 错误: 编码"GBK"的字符0x0xaa 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:11: 错误: 编码"GBK"的字符0x0xad 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:14: 错误: 编码"GBK"的字符0x0xb8 0x27在编码"UTF8"没有相对应值 psql:insert_data.sql:18: 错误: 编码"GBK"的字符0x0xa2 0x85在编码"UTF8"没有相对应值 psql:insert_data.sql:20: 错误: 编码"GBK"的字符0x0xaf 0x32在编码"UTF8"没有相对应值 psql:insert_data.sql:21: 错误: 编码"GBK"的字符0x0xa4 0x90在编码"UTF8"没有相对应值 psql:insert_data.sql:22: 错误: 编码"GBK"的字符0x0xac 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:29: 错误: 编码"GBK"的字符0x0xac 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:35: 错误: 编码"GBK"的字符0x0xab 0x27在编码"UTF8"没有相对应值 psql:insert_data.sql:37: 错误: 编码"GBK"的字符0x0xab 0x27在编码"UTF8"没有相对应值 psql:insert_data.sql:41: 错误: 编码"GBK"的字符0x0xab 0xe9在编码"UTF8"没有相对应值 INSERT 0 1 INSERT 0 1 INSERT 0 1 psql:insert_data.sql:46: 错误: 编码"GBK"的字符0x0x9c 0x27在编码"UTF8"没有相对应值 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 psql:insert_data.sql:53: 错误: 编码"GBK"的字符0x0xaf 0x27在编码"UTF8"没有相对应值 psql:insert_data.sql:57: 错误: 编码"GBK"的字符0x0xa4 0x90在编码"UTF8"没有相对应值 psql:insert_data.sql:59: 错误: 编码"GBK"的字符0x0xa4 0x90在编码"UTF8"没有相对应值 psql:insert_data.sql:60: 错误: 编码"GBK"的字符0x0xa4 0x90在编码"UTF8"没有相对应值 psql:insert_data.sql:61: 错误: 编码"GBK"的字符0x0xa4 0x90在编码"UTF8"没有相对应值 INSERT 0 1 psql:insert_data.sql:68: 错误: 编码"GBK"的字符0x0xaf 0xe7在编码"UTF8"没有相对应值 psql:insert_data.sql:73: 错误: 编码"GBK"的字符0x0xab 0xe9在编码"UTF8"没有相对应值 psql:insert_data.sql:74: 错误: 编码"GBK"的字符0x0xac 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:75: 错误: 编码"GBK"的字符0x0xac 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:77: 错误: 编码"GBK"的字符0x0xac 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:78: 错误: 编码"GBK"的字符0x0xac 0xe5在编码"UTF8"没有相对应值 psql:insert_data.sql:81: 错误: 编码"GBK"的字符0x0xac 0xe5在编码"UTF8"没有相对应值
使用 TRUNCATE 命令将这些表清空.
testbar=> truncate bars,beers,drinkers,frequents,likes,sells; TRUNCATE TABLE
检查客户端编码设置.
testbar=> SHOW client_encoding; client_encoding ----------------- GBK (1 行记录)
也可以使用下面的命令查看.
testbar=> select name,setting,context from pg_settings where name like '%encoding%';
name | setting | context
-----------------+---------+----------
client_encoding | GBK | user
server_encoding | UTF8 | internal
(2 行记录)
解决方案之一是修改客户端编码方式和服务端一致.
testbar=> \encoding UTF8
testbar=> select name,setting,context from pg_settings where name like '%encoding%';
name | setting | context
-----------------+---------+----------
client_encoding | UTF8 | user
server_encoding | UTF8 | internal
(2 行记录)
但是这个重启后设定就又恢复到原来的样子.
解决方案之二, 是将导入的数据用 GBK 编码保持.
现在使用 insert_data_GBK.sql 文件重新导入数据.
PS D:\work\cs\Database\postgres\testbar> psql -U haife -d testbar -f insert_data_GBK.sql INSERT 0 1 INSERT 0 1 INSERT 0 1 ... ... INSERT 0 1 INSERT 0 1 INSERT 0 1 PS D:\work\cs\Database\postgres\testbar>
再导入数据时, 确保psql能找到此文件. PostgreSQL 中使用 \! 命令切换到操作系统的命令行窗口.
testbar=> \! Microsoft Windows [版本 10.0.19045.6396] (c) Microsoft Corporation。保留所有权利。 C:\Users\haife>
比如这里, psql 登录时的路径是 C:\Users\haife, 那么就找不到 insert_data_GBK.sql, 因其位于 D:\work\cs\Database\postgres\testbar
切换到此目录
C:\Users\haife>cd D:\work\cs\Database\postgres\testbar C:\Users\haife>d: D:\work\cs\Database\postgres\testbar>
现在将 Frequents.csv 文件导入到数据库 frequents 中.
当然首先确保数据文件是 .csv 文件. 例如这里的文件 Frequents.txt, 其内容已经是csv格式, 但后缀最好改为 .csv
D:\work\cs\Database\postgres\testbar>move Frequents.txt Frequents.csv 移动了 1 个文件。
使用 exit 退出命令行界面.
D:\work\cs\Database\postgres\testbar>exit testbar=>
当然, 则 PostgreSQL 中可以直接使用 \cd 命令切换目录. 但要注意, 不能使用反斜杠.
testbar=> \cd D:\work\cs\Database\postgres\testbar 无效的命令 \work 使用\?获取帮助. testbar=> \cd D:/work/cs/Database/postgres/testbar testbar=>
使用 psql 命令导入 csv 文件时, 需要在最后加上 CSV.
testbar=> \copy frequents FROM Frequents.csv 错误: 对于字符类型来说这个值太长了(20) 背景: COPY frequents, line 1, column drinker: ""Charles Babbage","3DArtBar""
testbar=> \copy frequents FROM Frequents.csv CSV COPY 16
检查一下 frequents 表中的内容.
testbar=> select * from frequents;
drinker | bar
----------------------+------------------
Charles Babbage | 3DArtBar
Tim Berners-Lee | HardRock
Lynn Conway | Westside
Edsger Dijkstra | HardRock
John Hennessy | 木板房啤酒吧
Tony Hoare | 宝莱纳餐厅
Donald Ervin Knuth | 3DArtBar
Gordon Moore | 苏荷酒吧
John von Neumann | 扬州老啤酒厂酒吧
David Patterson | 扬州老啤酒厂酒吧
Claude Shannon | 木板房啤酒吧
Herbert A. Simon | Westside
Guy Steele Jr. | 3DArtBar
Gerald Jay Sussman | 宝莱纳餐厅
Alan Turing | HardRock
Andrew Chi-Chih Yao | 宝莱纳餐厅
(16 行记录)
请完成其他两张表内容的载入.
将文件 Drinkers_GBK.csv 中的内容导入到 drinkers 表.
testbar=> select * from drinkers; name | addr | phone ------+------+------- (0 行记录) testbar=> \copy drinkers FROM Drinkers_GBK.csv CSV COPY 16 testbar=>
testbar=> select * from drinkers;
name | addr | phone
---------------------+--------------------------------------------------------------------------------------------+-----------------
Charles Babbage | 英国伦敦波特兰广场德文郡街5号 |
Tim Berners-Lee | 32 Vassar Street MIT Room 32-G524 Cambridge MA 02139 USA | +1(617)253 5702
Lynn Conway | 密歇根州安娜堡市密歇根大学3640 CSE 大楼 | 48109-2121
Edsger Dijkstra | 荷兰Nuenen |
John Hennessy | Building 10, situated in the Inner Quad at the heart of the Stanford campus. | (650) 723-2481
Tony Hoare | |
Donald Ervin Knuth | 美国威斯康星州密尔沃基 |
Gordon Moore | 美国加利福尼亚州旧金山 |
John von Neumann | 匈牙利布达佩斯 |
David Patterson | 美国加州柏克莱市 |
Claude Shannon | 密歇根州佩托斯基 |
Herbert A. Simon | 德国达姆施塔特 |
Guy Steele Jr. | 美国密苏里州 |
Gerald Jay Sussman | 美国麻省理工学院 |
Alan Turing | 英国剑桥大学 |
Andrew Chi-Chih Yao | Institute for Interdisciplinary Information Sciences,Tsinghua University,Beijing,P.R.China |
(16 行记录)
将文件 Likes_GBK.csv 中的内容导入到 likes 表.
testbar=> \copy likes FROM Likes_GBK.csv CSV
COPY 20
testbar=> SELECT * FROM LIKES;
drinker | beer
---------------------+---------------
Charles Babbage | 百威
Charles Babbage | 嘉士伯
Charles Babbage | 米勒
Tim Berners-Lee | 科罗娜
Tim Berners-Lee | 麒麟
Lynn Conway | Stella Artois
Lynn Conway | 贝克
Edsger Dijkstra | 南非啤酒
John Hennessy | 百威
Tony Hoare | 南非啤酒
Donald Ervin Knuth | 安贝夫
Gordon Moore | 健力士黑啤
John von Neumann | 喜力
David Patterson | 朝日
Claude Shannon | 健力士黑啤
Herbert A. Simon | 健力士黑啤
Guy Steele Jr. | 安贝夫
Gerald Jay Sussman | 纯种苦啤酒
Alan Turing | 生力
Andrew Chi-Chih Yao | 生力
(20 行记录)