postgresql使用笔记

在terminal使用createuser命令时收到报错：

createuser: 无法联接到数据库 postgres: 致命错误:  角色 "lily" 不存在

解决方法：

sudo - su postgres切换到postgres用户。

在postgres用户下可使用createdb命令创建用户：

$ createuser --interactive lily
新的角色是否是超级用户? (y/n) y

--interactive选项可收到对所创建用户权限的询问，从而设置新创建用户的权限。

创建管理员名字的用户并授相应权限后则可成功在terminal直接使用createdb命令：

$ createuser neil
$ dropuser neil

 

查看已创建的角色名

可在postgres用户下使用psql命令进入数据库管理状态。输入命令：

# SELECT rolname FROM pg_roles;

可查看已有的角色名。

 

建立、登录数据库

创建用户后可在自己的用户下运行命令：

$ createdb bpsimple 

创建数据库bpsimple

成功创建后可运行命令：

psql -U lily -d bpsimple

以自己的用户身份登录数据库

 

运行sql脚本

psql数据库名进入管理，输入\i+脚本文件.sql运行脚本。

 

查看数据库中的表

psql 数据库名进入管理，输入\dt查看已有表：

 

bpsimple=# \dt
                关联列表
 架构模式 |   名称    |  类型  | 拥有者 
----------+-----------+--------+--------
 public   | barcode   | 数据表 | lily
 public   | customer  | 数据表 | lily
 public   | item      | 数据表 | lily
 public   | orderinfo | 数据表 | lily
 public   | orderline | 数据表 | lily
 public   | stock     | 数据表 | lily
(6 行记录)

bpsimple=# 

 

设置日期格式datestyle

psql后可使用SET datestyle命令修改日期格式，使用命令SHOW datestyle可查看内部datestyle变量。

bpsimple=# SELECT cast('2005-02-1' AS date);
    date    
------------
 2005-02-01
(1 行记录)

bpsimple=# SET datestyle TO 'US, SQL';
SET
bpsimple=# SELECT cast('2005-02-1' AS date);
    date    
------------
 02/01/2005
(1 行记录)

bpsimple=# SHOW datestyle;
 DateStyle 
-----------
 SQL, MDY
(1 行记录)

bpsimple=# SET datestyle TO 'European, SQL';
SET
bpsimple=# SELECT cast('2005 02 1' AS date);
    date    
------------
 01/02/2005
(1 行记录)

bpsimple=# SET datestyle TO 'European, ISO';
SET
bpsimple=# SELECT cast('2005-02-1' AS date);
    date    
------------
 2005-02-01
(1 行记录)

bpsimple=# 

 也可修改时间输出样式：

bpsimple=# 
bpsimple=# SELECT cast('2005-02-1' AS timestamp);
      timestamp      
---------------------
 2005-02-01 00:00:00
(1 行记录)

bpsimple=# SET datestyle TO 'European, Postgres';
SET
bpsimple=# SELECT cast('2005-02-1' AS timestamp);
        timestamp         
--------------------------
 Tue 01 Feb 00:00:00 2005
(1 行记录)

 

\copy命令

\copy 表名 FROM '文件名'
    [USING DELIMITERS '作为分隔符的单个字符']
    [WITH NULL AS '代表NULL的字符串']

 

访问序列生成器

定义为serial类型的列有一个序列生成器 <表名>_<列名>_seq。可通过currval函数获取序列生成器，nextval函数给序列生成器递增1，setval函数设置序列生成器。

e.g. customer表中有serial类型customer_id列。

查看序列生成器当前值：

bpsimple=# SELECT currval('customer_customer_id_seq');
 currval 
---------
      16
(1 行记录)

递增序列生成器：

bpsimple=# SELECT nextval('customer_customer_id_seq');
 nextrval 
---------
      17
(1 行记录)

使用聚集找出当前列中最大值：

bpsimple=# SELECT max(customer_id) FROM customer;
 max 
-----
  19
(1 行记录)

设置序列生成器的值：

bpsimple=# SELECT currval('customer_customer_id_seq');
 currval 
---------
      19
(1 行记录)