Table of contents


Command Line

用使用者帳號username登入指定的databalse

$ psql -U username -W -d database

Welcome to psql 8.3.21, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
  • \q 離開psql指令模式
  • \h SQL 指令的 help
  • \? psql 指令的 help

進入後,提示符號會變成資料庫名稱 + -# ,ex : mydb-#,後面的提示符如果是在 mydb-# 表示已進入 psql command mode環境

mydb-# \?

General
  \list 列出所有的database
  # 重新連線到特定的db (目前在"mydb")	
  \c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
                 connect to new database (currently "mydb")
  \cd [DIR]      change the current working directory
  \copyright     show PostgreSQL usage and distribution terms
  \encoding [ENCODING]
                 show or set client encoding
  \h [NAME]      help on syntax of SQL commands, * for all commands
  \prompt [TEXT] NAME
                 prompt user to set internal variable
  \password [USERNAME]
                 securely change the password for a user
  \q             quit psql
  \set [NAME [VALUE]]
                 set internal variable, or list all if no parameters
  \timing        toggle timing of commands (currently off)
  \unset NAME    unset (delete) internal variable
  \! [COMMAND]   execute command in shell or start interactive shell

Query Buffer
  \e [FILE]      edit the query buffer (or file) with external editor
  \g [FILE]      send query buffer to server (and results to file or |pipe)
  \p             show the contents of the query buffer
  \r             reset (clear) the query buffer
  \w FILE        write query buffer to file

Input/Output
  \echo [STRING] write string to standard output
  \i FILE        execute commands from file
  \o [FILE]      send all query results to file or |pipe
  \qecho [STRING]
                 write string to query output stream (see \o)

Informational
  # 顯示schema
  \d [NAME]      describe table, index, sequence, or view
  # \dt 列出目前db所有的tables
  # \di 列出目前db所有的indexs
  # \ds 列出目前db所有的sequences
  # \dv 列出目前db所有的views
  # \dS 列出目前db所有的system tables
  \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
                 list tables/indexes/sequences/views/system tables
  \da [PATTERN]  list aggregate functions
  \db [PATTERN]  list tablespaces (add "+" for more detail)
  \dc [PATTERN]  list conversions
  \dC            list casts
  \dd [PATTERN]  show comment for object
  \dD [PATTERN]  list domains
  \df [PATTERN]  list functions (add "+" for more detail)
  \dF [PATTERN]  list text search configurations (add "+" for more detail)
  \dFd [PATTERN] list text search dictionaries (add "+" for more detail)
  \dFt [PATTERN] list text search templates
  \dFp [PATTERN] list text search parsers (add "+" for more detail)
  \dg [PATTERN]  list groups
  \dn [PATTERN]  list schemas (add "+" for more detail)
  \do [NAME]     list operators
  \dl            list large objects, same as \lo_list
  \dp [PATTERN]  list table, view, and sequence access privileges
  \dT [PATTERN]  list data types (add "+" for more detail)
  \du [PATTERN]  list users
  \l             list all databases (add "+" for more detail)
  \z [PATTERN]   list table, view, and sequence access privileges (same as \dp)

Formatting
  \a             toggle between unaligned and aligned output mode
  \C [STRING]    set table title, or unset if none
  \f [STRING]    show or set field separator for unaligned query output
  \H             toggle HTML output mode (currently off)
  \pset NAME [VALUE]
                 set table output option
                 (NAME := {format|border|expanded|fieldsep|footer|null|
                 numericlocale|recordsep|tuples_only|title|tableattr|pager})
  \t             show only rows (currently off)
  \T [STRING]    set HTML <table> tag attributes, or unset if none
  \x             toggle expanded output (currently off)

Copy, Large Object
  \copy ...      perform SQL COPY with data stream to the client host
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID    large object operations	

列出目前所在的database的所有 tables

\d{t i s v S} [PATTERN] (add “+” for more detail)
  • \dt 列出目前db所有的tables
  • \di 列出目前db所有的indexs
  • \ds 列出目前db所有的sequences
  • \dv 列出目前db所有的views
  • \dS 列出目前db所有的system tables

ex:

mydb-# \dt

           List of relations
 Schema |     Name      | Type  | Owner
--------+---------------+-------+-------
 public | auth          | table | admin
 public | match_history | table | admin
 public | usr           | table | admin
(3 rows)

顯示某個table,view,index的schema

\d [NAME] describe table, index, sequence, or view

ex:

mydb-# \d auth
                                      Table "public.auth"
   Column    |            Type             |                     Modifiers
-------------+-----------------------------+---------------------------------------------------
 id          | integer                     | not null default nextval('auth_id_seq'::regclass)
 create_date | timestamp without time zone |
 enabled     | boolean                     | not null
 expire_date | timestamp without time zone |
 feature_id  | integer                     |
 hwid        | character varying(255)      |
 match_date  | timestamp without time zone |
 matchable   | boolean                     | not null
 max_device  | integer                     |
 printed     | boolean                     | not null
 sn          | character varying(255)      | not null
 trial       | boolean                     | not null
 agent_id    | integer                     |
 user_id     | integer                     |
 version     | character varying(20)       | not null default 1
 edition     | character varying(255)      |
Indexes:
    "auth_pkey" PRIMARY KEY, btree (id)
    "auth_sn_key" UNIQUE, btree (sn)	

expanded mode (將資料顯示的方式轉90度)

-x : Turn on the expanded table formatting mode

正常的情況下查詢出來的資料,欄位是以水平方式排列

mydb=# select id,author from databasechangelog;
 id | author 
----+--------
 1  | bob
(1 row)

透過\x參數可以把讓資料顯示方式變成垂直排列,這對於資料欄位較多或資料較長時,layout會亂掉,如果用expanded mode就可以有比較好的效果

mydb=# \x
Expanded display is on.
mydb=# select id,author from databasechangelog;
-[ RECORD 1 ]
id     | 1
author | bob

Sequence (AUTO INCREMENT)

PostgreSQL並沒有自增型欄位型態(AUTO INCREMENT),但是有提供Sequence機制,這部份,與oracle比較類似,但PostgreSQL可以透過建立table時的自訂語法來自動套用Sequence,讓其動作比較接近AUTO INCREMENT的效果

更新: 型態選用serial即可有 auto increment 的功能,在執行不指定id欄位的insert後,每個資料列會自動填入 +1 的序號值,不需額外的處理

使用方式

讓table的某一column套用sequence
CREATE TABLE tablename (
    colname SERIAL
);

上面的語法等效於

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer DEFAULT NEXTVAL('tablename_colname_seq') NOT NULL
);

建立sequence
create sequence seq start 100;

以上語法會建立一個名為seq的sequence,啟始值為100 (從100起跳)

取得目前sequence的值
select currval('seq');

取得下一個sequence的值
select nextval('seq');

重新設定sequence值
select setval('seq',9)

重新設定sequence值(建議)
select setval('seq', max(id)) FROM myTable;

Drop all tables script

/* create a function for drop all tables */
CREATE OR REPLACE FUNCTION drop_all_table() RETURNS SETOF RECORD   AS $$
DECLARE
  stmt RECORD;
BEGIN
    FOR stmt IN
        /* select table */
       SELECT c.relname FROM pg_catalog.pg_class AS c LEFT JOIN
    pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind =
    'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
    pg_catalog.pg_table_is_visible(c.oid)
     LOOP
        /* drop table one by one */
        EXECUTE 'drop table ' || quote_ident(stmt.relname) || ';';
    END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
 
begin;
/* execute the function */
SELECT drop_all_table();
commit;

Truncate all tables script

/* create a function for drop all tables */
CREATE OR REPLACE FUNCTION truncate_all_table() RETURNS SETOF RECORD   AS $$
DECLARE
  stmt RECORD;
BEGIN
    FOR stmt IN
        /* select table */
       SELECT c.relname FROM pg_catalog.pg_class AS c LEFT JOIN
    pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind =
    'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
    pg_catalog.pg_table_is_visible(c.oid)
     LOOP
        /* truncate table one by one */
        EXECUTE 'truncate table ' || quote_ident(stmt.relname) || ';';
    END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
 
begin;
/* execute the function */
SELECT  truncate_all_table();
commit;

如果遇到建立script時language “plpgsql” does not exist(通常在linux下才會),可透過createlang命令,安裝plpgsql到指定的db

createlang -d <dbname> plpgsql

建立全新的Database

從完全沒有db開始建立的流程如下

  • pgsql/bin/initdb.exe -D path
  • pathpg_hba.conf 設定local可以不用密碼登入(暫時性的,等設定完成記得改回來)
  • 用os的帳號登入新的db (通常是administrator),因為上面已設定不用密碼了,所以只要os的帳號即可登入
  • 登入後再建立新的database跟user
  • pathpg_hba.conf改回需認證才能登入

管理

列出目前活動
select * from pg_stat_activity ;

 datid | datname | procpid | usesysid | usename | current_query
-------+---------+---------+----------+---------+---------------
 16976 | simpydb |   31008 |      100 | otis    |
 16976 | simpydb |   26126 |      100 | otis    |
(2 rows)

Resource