PostgreSQL常用命令

檢視版本資訊

mydb=# SELECT version();
                                                version                                                
-------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.6 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit
(1 row)

建立資料庫

mydb=# CREATE DATABASE test;
CREATE DATABASE

顯示所有資料庫

mydb-# \l
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges  
-----------+---------+----------+-------------+-------------+---------------------
 mydb      | postgre | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres  | postgre | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgre | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgre         +
           |         |          |             |             | postgre=CTc/postgre
 template1 | postgre | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgre         +
           |         |          |             |             | postgre=CTc/postgre
 test      | postgre | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(5 rows)

切換當前資料庫

mydb-# \c test
Password for user postgre: 
You are now connected to database "test" as user "postgre".

刪除資料庫

# 不能刪除當前連線的資料庫
test=# DROP DATABASE test;            
ERROR:  cannot drop the currently open database

test=# DROP DATABASE mydb;
DROP DATABASE

列舉當前資料庫表

test=# create table t as select * from pg_tablespace;
SELECT 2

test=# \dt
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+---------
 public | t    | table | postgre
(1 row)

檢視錶結構

test=# \d t
          Table "public.t"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | 
 spcowner   | oid       | 
 spcacl     | aclitem[] | 
 spcoptions | text[]    |

重新命名錶

test=# alter table t rename to t_t;
ALTER TABLE

test=# \dt
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+---------
 public | t_t  | table | postgre
(1 row)

檢視錶索引

# 建立索引
test=# create index idx_name on t (spcname);
CREATE INDEX

# 顯示索引
test=# \di ;
              List of relations
 Schema |   Name   | Type  |  Owner  | Table 
--------+----------+-------+---------+-------
 public | idx_name | index | postgre | t
 public | idx_t    | index | postgre | t_t
(2 rows)

刪除表

test=# drop table t_t;
DROP TABLE

建立使用者

test=# CREATE USER svoid;
CREATE ROLE
# 建立sovid 指定密碼
test=# CREATE USER svoid WITH PASSWORD '123qwe';
CREATE ROLE

檢視系統使用者資訊

test=# SELECT usename FROM pg_user;
 usename 
---------
 postgre
 svoid
(2 rows)

test=# \du;
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgre   | Superuser, Create role, Create DB, Replication | {}
 svoid     |                                                | {}

刪除使用者

test=# DROP USER svoid;
DROP ROLE

檢視schema

test=# \dn;
 List of schemas
  Name  |  Owner  
--------+---------
 public | postgre
(1 row)

建立schema

test=# CREATE SCHEMA myschema;
CREATE SCHEMA

刪除schema

test=# DROP SCHEMA myschema;
DROP SCHEMA

待補充。。。

延伸閱讀：
http://www.cnblogs.com/stephen-liu74/archive/2012/06/08/2315679.html

整理自網路