Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
PostgreSQL是基於POSTGRES 4.2的物件導向關連式資料庫管理系統,其由美國加州伯克萊大學資訊科學系所研發。POSTGRES所開發的許多重要概念成為許多日後商用資料庫系統重要的一部份。
PostgreSQL由伯克萊大學公開其原始碼所誕生,它支援了大多數的標準SQL語法,並提供許多先進的功能:
complex queries
foreign keys
triggers
updatable views
transactional integrity
multiversion concurrency control
同時,PostgreSQL也支援讓使用者能以自己的方式善用資料庫系統:
data types
functions
operators
aggregate functions
index methods
procedural languages
在使用權利方面,不論任何人以任何目的,使用、修改、散布PostgreSQL,都是被允許的,包含私人使用、商業用途、或學術研究。
本篇談的是如何回報問題到 PostgreSQL 官方組織,而本正體中文手冊並非由官方提供,所以如果你希望指出的問題是本手冊的相關問題,請透過討論區,或台灣 PostgreSQL 使用者社群所提供的聯絡資訊回報。
如果你在 PostgreSQL 中發現了問題,我們會很希望可以得到通知。你的問題回報可以讓 PostgreSQL 變得更值得信任,因為百密仍有一疏,PostgreSQL 無法保證在任何平台或任何情況下,都一定是完美無缺的。
下面的建議提供你在回報問題時能夠更有效率。你不一定要完全遵照下面的方式,但如果你試著遵循的話,對大家都有幫助。
我們無法保證可以立即修正所有的錯誤。但如果那個問題是明顯的、關鍵的、或是有重大影響的,那就會有人進行瞭解。也可能會回覆你更新你的資料庫版本,如果是因為版本問題的話。我們也可能會判定該錯誤不會被修正,在我們進行重大修改之前;又也許它不容易簡單處理,而且有其他更重要的需求排程已經在進行中。如果你需要立即性的支援,請接洽當地的商業服務。
在報告錯誤之前,請再三閱讀文件,以確認你真的在進行你正在嘗試的事情。 如果從文件中不清楚是否可以做某事,請回報;這是屬於文件的一個錯誤。如果確實證明一個程式與文件所描述地不同,那就是一個錯誤。這可能包括但不限於以下情況:
程式以致命信號(fatal signal)或程式中某個問題造成作業系統錯誤訊息而終止。(反例可能是“磁盤已滿”的訊息,因為您必須自己修復。)
程式對於任何輸入都產生錯誤輸出結果。
程式拒絕接受有效的輸入 (如文件中所定義的)。
程式接受了無效的輸入,卻沒有警告或錯誤消息。但請記住,您對於無效輸入的認知可能來自於我們對傳統做法的延伸或相容性。
PostgreSQL 在支援的平台上,按照指示進行編譯,構建或安裝卻失敗了。
這裡的「程式」是指任何可執行文件,不僅僅是後端執行的程序。
緩慢或資源匱乏不一定是一個錯誤。閱讀文件或在某個郵件列表中提問,可以幫助你調整應用程式。不符合 SQL 標準不代表是錯誤,除非明確聲明相容某個特定的功能。
在繼續之前,請檢查 TODO 列表和常見問題解答,看看您的錯誤是否已知。 如果您無法瞭解 TODO 列表中的資訊,請報告你的問題。 我們至少可以做的是使 TODO 列表更清楚。
關於錯誤報告最重要的是陳述所有事實並且只有事實。不要揣測你的想法是錯的,什麼「似乎」,或程式的哪個部分有故障。如果你不熟悉實作的方法,你可能會猜測錯誤,而無法幫助我們。即使你有知識性的解釋,也應該是對事實的補充而不是替代它們。如果我們要修復這個錯誤,我們還是首先要看到它發生。報告裸露的事實是相對簡單的(你可以從屏幕上複製貼上它們),但是經常重要的細節被忽略,因為有人認為這並不重要,或者認為報告被理解是應當的。
每個錯誤報告中都應包含以下內容:
程式執行步驟的確切順序是重現問題所必需的。這應該是獨立的;如果輸出應該依賴於資料表中的資料,那麼沒有包含前面的 CREATE TABLE 和 INSERT 語句的 SELECT 語句是不夠的。我們沒有時間來對你的資料庫進行逆向工程,如果我們需要建立自己的資料庫的話,我們很可能會錯過這個問題。
SQL相關問題的測試最佳格式是可以透過 psql 執行並重現問題。 (請確認您的 ~/ .psqlrc 啟動設定中沒有任何內容。)建立這個檔案的一個簡單方式是使用 pg_dump 來轉出設定該情境的資料表宣告及資料,然後加入產生問題的查詢語句。我們希望您盡量減少您的問題規模,但這並不是絕對必要的。如果錯誤是可重現的,我們會以任何一種方式找到它。
如果您的應用程式使用某些其他客戶端界面(如PHP),請嘗試突顯那些問題查詢語句。我們應該不會設置一個 Web 伺服器來重現您的問題。無論如何,請記得提供確切的輸入檔案;不要猜測「大文件」或「中型數據庫」等問題的可能性。因為這些訊息不夠精確,沒有參考價值。
你所得到的輸出。請不要說「不能用」或「壞掉了」。如果出現錯誤訊息,請列出來,即使您並不瞭解它。如果程式終止是因為作業系統錯誤,請說明哪個系統錯誤。如果沒有發生任何事情,也如實說明。即使您的測試案例的結果是當機或其他明顯的情況,也不一定會在我們的平台上發生。如果可以的話,最簡單的方法是從終端視窗中複製輸出內容。
注意如果您要報告錯誤訊息,請取得該訊息最詳細的形式。在 psql,事先輸入
\set VERBOSITY verbose
。 如果從伺服器記錄取得訊息,請在執行時將參數 log_error_verbosity 設定為 verbose,以便記錄所有詳細訊息。如果是嚴重錯誤的情況下,客戶端報告的錯誤訊息可能不包含所有可用的信息,還請查看資料庫伺服器的系統記錄輸出。如果你沒有保留伺服器的系統記錄,那麼這是開始這樣做的好時機。
你所期待的輸出情況對於情境說明非常重要。如果你只是寫「這個命令給我這樣的輸出」或「這結果不是我的預期」,我們可以自己運行它,檢視該輸出結果,並認為它看起來正常,正是我們的預期。我們不應該把時間花在解讀你的命令背後確切的語意。特別是不要僅僅說「這不是 SQL 或 Oracle 所做的那樣」。從 SQL 挖掘所謂正確的行為並不是一件有趣的事情,我們更不會知道所有其他關連式資料庫的做法。(如果你的問題是當機,很明顯地你可以省略此項。)
任何命令列選項和其他啟動選項,包括您從預設值修改的任何相關的環境變數或設定檔案。再次提醒,請提供確切的訊息。如果您正在使用預先封裝好的套件,在開機時啟動資料庫伺服器,您應該嘗試瞭解它如何進行。
所有你所做的與安裝說明不同之處。
PostgreSQL 的版本。你可以執行SELECT version();
來找到你正在連線的資料庫系統版本。大多數的程式工具也會支援--version
選項;至少postgres --version
和psql --version
都可以使用。如果功能或選項不存在,那麼您的版本已經太舊而無法進行升級。如果您運行預先編譯的套件(如 RPM),請說明,包括該套件可能具有的子版本。如果您正在使用 Git 某個快照,請說明快照版本,包括提交碼(commit hash)。
如果您的版本比10還舊,我們幾乎肯定會告訴您進行升級。每個新版本都有很多錯誤修復和改進,所以很可能在PostgreSQL的舊版本中遇到的錯誤已經被修復了。我們只能對使用較早版本的PostgreSQL伺服器提供有限的支援;如果你的需求多於我們所能提供的,請考慮取得商業支援合約。
執行平台資訊。這包括作業系統核心名稱和版本,C語言函式庫,中央處理器、記憶體資訊等等。在大多數情況下,報告系統供應商和版本是足夠的,但不要假設每個人都知道「Debian」究竟是什麼,或是每個人都運行在 i386 上。如果您有安裝問題,則還需要你的機器上有關系統工具組的訊息(編譯器、make 工具等等)。
不要擔心你的錯誤報告會因此而變得冗長。這是一個事實過程的呈現。第一次報告所有事情,對我們比較好的做法是,儘量把事實從你身上擠出來。 另一方面,如果你輸入的檔案很大,持平來說,首先要問是否有人有興趣去研究它。這裡有一篇文章,概述了有關報告錯誤的更多建議。
不要花所有的時間來指出輸入中的哪些變化會使問題消失。這可能對解決問題沒有幫助。如果事實證明該錯誤不能立即解決,您仍然有時間找到並分享您的解決方案。此外,再一次,不要浪費你的時間猜測為什麼這個問題會存在。我們會很快找到。
在撰寫錯誤報告時,請避免混淆術語。這個軟體整體來說被稱為「PostgreSQL」,有時候會簡稱為「Postgres」。如果你特別在談論後端的程序,請明確指出,而不要只是說「PostgreSQL 當掉了」。其中一個後端程序的當掉與主要的「postgres」程序當掉有很大的不同;當你的意思是某個後端程序終止時,請不要說「伺服器當掉了」,反之亦然。此外,例如交互式前端的「psql」用戶端程序與後端完全分離的。請嘗試具體說明問題是在用戶端還是伺服器端。
一般來說,將錯誤報告發送到錯誤報告的郵件列表:<
pgsql-bugs@postgresql.org
>
。你需要為你的電子郵件使用描述性主題,可能是錯誤消息的一部分。
另一種方法是填寫官方網站上提供的錯誤報告網頁式表單。用這個方式的話,它也會把內容轉寄到<
pgsql-bugs@postgresql.org
>
郵件列表之中。
如果您的錯誤報告隱含有安全疑慮,並且您希望它不會在公共討論區中立即顯示,請不要將其發送到pgsql-bugs。安全問題可以非公開地回報至<
security@postgresql.org
>
。
不要向任何用戶的郵件列表發送錯誤報告,像是<
pgsql-sql@postgresql.org
>
或<
pgsql-general@postgresql.org
>
。這些郵件列表用於回答用戶的問題,用戶通常不希望收到錯誤報告。 更重要的是,他們不太可能修復它們。
另外,請不要向開發者的郵件列表發送報告<
pgsql-hackers@postgresql.org
>
。這個列表用於討論 PostgreSQL 的開發,如果我們可以將問題報告分開來,那將是很棒的。 不過,我們可能會選擇討論你的錯誤報告pgsql-hackers
,如果那個問題需要更多討論的話。
如果你的問題是使用手冊,回報問題的最佳位置是使用手冊的郵件列表<
pgsql-docs@postgresql.org
>
。請具體說明文件的哪一部分讓你不滿意。
如果您的錯誤是非支持平台上的可移植性問題,請寄送郵件到<
pgsql-hackers@postgresql.org
>
,讓我們(和你)可以將 PostgreSQL移植到你的平台上。
注意不幸由於垃圾郵件數量,所有上述電子郵件地址都是封閉的郵件列表。也就是說,您需要訂閱列表才能發布。(然而,您不需要訂閱使用錯誤報告Web表單。)如果你想發送郵件但不想接收郵件列表,您可以訂閱並設置您的訂閱選項為「nomail」。想得到更多訊息的話,請寄一封郵件到
<
majordomo@postgresql.org
>,內文請輸入「help」一個單字,就會自動回覆你進一步的訊息。
歡迎來到 PostgreSQL 的新手教學。在這個部份裡的內容,主要提供有關於 PostgreSQL 各項功能的簡介、關連式資料庫概念、以及 SQL 語法的入門說明。我們只假設您俱備一些電腦系統基本操作,並不需要很專業的 Unix 或程式設計經驗。這裡主要提供一些實用的經驗,還有 PostgreSQL 系統中重要部份的介紹。在這個部份並不會進行所有議題的詳細說明。
在你閱讀完新手教學之後,也許可以繼續閱讀:更多有關於 SQL 語法的標準知識;或者到:瞭解如何開發 PostgreSQL 的應用程式;而如果你需要建置及管理你的資料庫伺服器的話,請參閱的內容。
本手冊為PostgreSQL官方手冊翻譯版,由PostgreSQL台灣社群愛好者所提供,描述本版本PostgreSQL的功能與支援情形。
為了更易於閱讀與管理,本手冊以下列數個部份所組成。每一個部份均為不同類型的使用者所撰寫:
:給新的使用者一份簡易的介紹。
:介紹SQL查詢語言,包含資料型態及函數功能,應用層級的效能調教也在此有所說明。每一個PostgreSQL使用者都推薦閱讀此部份。
:說明資料庫伺服器的安裝及管理資訊。如果你需要管理一個PostgreSQL伺服器,那你必須閱讀此部份的內容。
:說明PostgreSQL用戶端的程式操作介面。
:資料庫伺服務的進階說明及延伸的使用方式,亦包含了使用者自訂的資料型別及函式。
:SQL查詢指令、用戶端指令及伺服器端指令在此部份詳細說明。
:提供PostgreSQL開發者可能需要的其他相關資訊。
PostgreSQL目前為眾所皆知的物件導向的關連式資料庫管理系統,其由美國加州伯克萊大學所研發的POSTGRES衍生而成。經過超過二十年以上的演進,PostgreSQL現在是世界上最先進的開源資料庫系統。
POSTGRES專案是由Michael Stonebraker教授領導的團隊進行研發,其受到Defense Advanced Research Projects Agency (DARPA),the Army Research Office (ARO),the National Science Foundation (NSF),及ESL, Inc的贊助。POSTGRES專案始於1986年,最原始的設計,"The design of POSTGRES",作為開端,其最初的資料結構模型則揭露於"The POSTGRES data model"。規則系統設計發表於"The design of the POSTGRES rules system",而當時的關連式資料儲存的架構則刊載於"The design of the POSTGRES storage system"。
POSTGRES接下進行了幾次重大的變革。第一代的"demoware"在1987年真的實作成為可用的系統,並在1988年的ACM-SIGMOD研討會中進行展示,並在1989年6月,釋出了第1版可供外部使用者使用的資料庫系統。為了回應當時使用者對於第一代規則系統的批評,其規則系統重新進行設計,並在隔年1990年的6月份,隨即推出第2版系統,搭載新的規則系統設計。第3版系統則於1991年發表,新增支援多重儲存管理機制,改善查詢處理器,並又改寫了規則系統。如此直到Postgres 95誕生之前,主要都專注於移植性及可信賴度的發展。
POSTGRES接下來開始被運用在許多不同的研究和產品上,財務資料分析系統、噴氣引擎效能監控系統、小行星追蹤資料庫、醫療資訊系統、以及數個地理資訊系統。POSTGRES也被好幾所大學用於其教學工具。最後,由Illustra Information Technologies(後來併入Informix,而Informix目前為IBM所擁有)技術移轉,並將其商業化。於1992年末,POSTGRES成為Sequoia 2000 scientific computing project主要的資料管理系統。
在1993年間,用戶數量呈現倍數成長,伴隨而來的是大量的程式碼維護與服務支援,占去絕大部份原來應該進行研究的時間。為了減少維運的負擔,伯克萊的POSTGRES專案正式終止於4.2版。
1994年,Andrew Yu和Jolly Chen在POSTGRES增加了SQL語法的直譯器,並且以新的Postgres 95為名,在網路上開放讓全世界的人使用。他們成為伯克萊POSTGRES原始碼最初的繼承者。
Postgres 95的程式碼是完全以ANSI C開發,並且輕量化了25%。許多內部的改良增進了效率及可維護性。當時Wisconsin Benchmark進行測試,Postgres 95在1.0.x時的效能比原始的POSTGRES 4.2快了約30%至50%。除了一些錯誤修正之外,還有下面這些主要的改良:
原有的PostQUEL以SQL(實作於伺服器端)所取代。(連接介面在PostQUEL之後便採libpq函式庫) 子查詢一直到PostgreSQL出現之前都還未支援,但在Postgres 95便已能使用自訂的SQL函數,聚合函數Aggregate function則被重新實作。GROUP BY查詢語句也在此時被加入。
新的工具psql可進行互動式的SQL操作,其採用的是GNU Readline的技術。psql開始大量取代老舊的管理工具。
新的前端函式庫,libpgtcl,支援Tcl-based用戶端程式。還有一個簡易的命令列介面工具pgtclsh,使用新的Tcl命令和Postgres 95伺服器進行操作。
重新改寫了large-object處理的交換介面,僅使inversion作為儲存大型物件的唯一機制。(inversion檔案系統就此移除)
Instance-level的規則系統被淘汰了,但其規則仍用於重構規則所使用。
製作了一個簡短的說明,介紹標準的SQL功能,並隨Postgres 95原始碼發佈。
使用GNU make(取代BSD make)編譯程式碼,Postgres 95也支援使用未修正的GCC編譯器(修正高精度資料對齊問題)。
1996年,"Postgres 95"這個名稱很明顯不再適合。我們選擇了新的名稱,PostgreSQL,其呈現出與原始POSTGRES之間的源由,也彰顯了結合SQL力量的意義。同時,我們設定其版本由6.0開始,重回伯克萊POSTGRES專案的版號序列。
許多人持續使用"Postgres"(現在已經很少使用全大寫字母表現)來代表PostgreSQL,是因為傳統,也可能是因為比較好發音。這樣的用法也廣為用於暱稱或別名。
Postgres 95的發展主要在於瞭解及定義伺服器程式既有的問題,而PostgreSQL則更重視系統的能力與爭議性的功能上,不過所有的工作是全面性的。
更多有關於PostgreSQL的發展,請參閱附錄E。
以下所提到慣例,用於指令的語法描述上(均為半型字元): 中括號( [ 和 ] )指可選擇是否輸入的選項。(在Tcl指令的語法中,習慣使用問號?表達這樣的可選擇性) 大括號( { 和 } )及垂直線( | )指的是必須要輸入的部份。 連續句點(...)指的是該段落可以允許不斷重覆。
為了使說明更簡潔,SQL指令使用提示字元"=>",作業系統命令列指令則使用"$"。雖然一般而言,提示字元可能不會顯示。
Administrator一般的定義是負責安裝及運行資料庫系統的人;User指的是任何正在使用資料庫的人,或者正要使用任何PostgreSQL相關系統的人。這些定義不應該被解釋得太過嚴格,在本文件中,對於系統管理的工作,並沒有固定的假設。
版本:11
在開始使用之前,你需要瞭解基本的PostgreSQL系統架構。認識PostgreSQL如何回應操作,有助於讓你更清楚瞭解以下的說明。
以資料庫的述語來說,PostgreSQL採用了主從式架構(client/server)。PostgreSQL會在進行下列操作時保持連線:
伺服器的執行緒,負責管理資料庫的檔案、受理用戶端的連線要求、執行相對應的資料庫動作。這樣的資料庫伺服端程式稱之為「postgres」。
用戶端的程式用來發起資料庫操作的行為,其設計的形態很廣泛:可能是文字介面的工具、圖型介面的程式、將資料庫內容顯示成網頁的網際網路伺服器、甚或是專用的資料庫管理工具。有一些用戶端程式是由PostgreSQL官方所提供,大部份由第三方的其他使用者所開發。
如同一般的主從式架構,用戶端與伺服端可以是兩台不同的主機,而他們透過TCP/IP的網路協定溝通。你應該將這個觀念謹記在心,因為某些在用戶端可以被存取的檔案,在伺服端可能就無法存取(或使用不同的檔案名稱)。
PostgreSQL伺服器可以管理來自多個用戶端的同步連線。為了達到這樣的功能,它會自我複製(fork)成新的執行緒,一對一地處理每一個連線。這個部份進一步來說,用戶端和新的伺服器執行緒之間的溝通,並不需要原始的postgres執行緒介入。也就是說,主要的資料庫服務執行緒會持續等待其他用戶端的連線,協助安排好其與伺服端執行緒的配對之後便完全交接,再回到等待的狀態。(當然,使用者完全不會察覺這些行為,在此說明僅僅是為了整體性的概念描繪)
版本:11
你需要先進行安裝,才能開始使用PostgreSQL。當然,PostgreSQL也可能已經被安裝在你的系統之中,因為你的作業系統預設套件包含了PostgreSQL,或其他系統管理者已先行安裝。如果是這樣的話,那麼你應該先瞭解作業系統的資訊,或向你的系統管理員取能存取的資訊。
如果你並不確定PostgreSQL是否已經可以使用,或者你也可以自行安裝試試。這樣做並不是很困難,而且是很好的操作練習。PostgreSQL可以以一般使用者進行安裝,它並不需要系統管理者(root)的權限才能安裝。
如果你打算自行安裝PostgreSQL,你可以參考第16章的指令進行,完成之後再回到這裡,以瞭解接下來關於設定環境變數的內容。
如果你的系統管理者並非以預設的方式安裝,你可能還有一些額外的工作要做。例如,如果資料庫主機其實是遠端的伺服器,你會需要設定PGHOST的環境變數,將其指向資料庫主機的網路名稱。而PGPORT變數也是必須要設定的。最基本的情境是,如果你嘗試啓動一個應用程式,而它回報它無法取得資料庫連線時,你就必須洽詢你的系統管理者。而如果系統管理者就是你自己,那麼你應該依文件再確認你的環境設定是正確的。如果你仍然並不清楚前面所描述的事項,請詳細閱讀的內容。
版本:11
:從無到有,安裝一個 PostgreSQL 資料庫系統。
:認識 PostgreSQL 的資料庫架構。
:建立第一個 PostgreSQL 資料庫。
:開始存取你的 PostgreSQL 資料庫。
版本:11
一旦你已經建立一個資料庫,你就可以開始以下列方式進行存取:
執行 PostgreSQL 互動式的終端程式,稱作 psql,它可以讓你輸入、編輯、執行 SQL 指令。
使用既有的圖型化介面工具,例如 pgAdmin 或是支援 ODBC 或 JDBC 的辦公室軟體,以建立並輸入資料到資料庫裡。不過這部份並未包含在這份手冊之中。
自行撰寫一個程式,可以使用許多種程式語言來完成。這個部份將會在第 IV 章中進行介紹。
在這份指南中,你可能會先使用 psql 來進行一些嘗試。你可以藉由下列指令開始操作 mydb 這個資料庫:
如果你並未指明資料庫名稱,那麼它預設會以你的使用者名稱作為資料庫名稱。在先前的章節使用 createdb 時,你已經知道這個隱含的規則了。
在 psql 中,你會以下列訊息開始:
最後一行也可能是:
這表示你是資料庫的超級使用者(superuser),如果你是自行安裝 PostgreSQL 的話,大概就會是這個情況。 作為一個超級使用者,表示你不會受限於任何存取控制。不過在這份指南中,這並不是重要的事。
如果你在啓動 psql 時遭遇了一些問題,那麼請回到前一節。createdb 和 psql 的行為很類似,如果前者正常,後者也應該如期運行。
最後一行會輸出的是 psql 的提示字串,它表示 psql 正在等待你輸入 SQL 查詢語句。試試下面的指令吧:
psql 程式中也內建了一些非 SQL 的命令。他們會以倒斜線( )起頭。舉例來說,你可以輸入下列指令以取得一些有關 PostgreSQL 所支援的 SQL 語法資訊:
要離開 psql 的話,請輸入:
如此的話,psql 將會結束,並回到你的命令列介面之中。(想瞭解更多內建指令,在 psql 提示字串後輸入 \? 。)完整的 psql 說明,都記載在 psql 頁面之中。在這份指南中,我們並未使用這些功能,但你可以在需要的時候使用他們。
版本:11
本章適合初學資料庫的朋友閱讀,以簡單的語法範例,實際操作以瞭解資料庫的運作方式。事實上,更複雜的資料庫行為,也不脫這個基本的操作模式。
本使用手冊由台灣 PostgreSQL 社群提供,翻譯自 PostgreSQL 官方使用手冊,以推廣 PostgreSQL 於台灣的應用。
本使用手冊目前編譯內容為 PostgreSQL 11,另外還有 FAQ 可以參考。
每一個頁面均附上官方手冊對應連結,翻譯未詳盡之處,可對照閱讀。 未翻譯完成之段落,將暫以原文(英文)替代。
閱讀前,也可以參閱摘要簡報: https://gitpitch.com/pgsql-tw/twpug-doc
目前最新版本為 PostgreSQL 11:(部份內容為版本10,持續更新中) https://medium.com/pgsql-tw/postgresql-11-press-kit-331df8357dcc
參與協作請在任何頁面,點選右上角的「Edit on GitHub」,修改後直接送 PR 給我們即可。 (只翻一句也可以唷!)
任何問題或建議可以 Email 給我們的文件小組:docs@postgresql.tw
版本:11
PostgreSQL 是一個關連式資料庫管理系統(RDBMS)。這表示它是一個管理關連性質資料的系統。關連性,基本上在數學裡是以資料表(table)的形式來表現的。今天,以資料表為形式儲存資料是很常見的事,它是很自然的表現,但也有很多其他組識資料庫的方式。在 Unix-like 的作業系統中,檔案和目錄是一個階層式資料庫的案例。更先進的發展是採用物件導向式的資料庫。
每一個資料表是很多資料列(row)的集合。而每一個資料列則以許多相同集合的欄位(column)所組成。每一個欄位都被指定了特定的資料型別。每一個資料列中欄位的次序是固定的。很重要且必須記得的是,SQL 並不保證資料列在資料表中的次序(雖然他們可以在顯示的時候被明確表現)。
一個資料庫中集合了許多資料表,而很多的資料庫則被一個 PostgreSQL 服務所管理,形成一個資料庫叢集。
版本:11
第一個測試確認你是否能夠存取一個資料庫服務,就是嘗試去建立一個資料庫。一個執行中的 PostgreSQL 服務可以管理許多個資料庫。一般來說,每一個專案或使用者會分開使用不同的資料庫。
你的系統管理員也可能已經為你建立了一個資料庫,如果是這樣的話,那你可以略過本節說明,直接進入到下一節的內容。
要建立一個新的資料庫,在本例中取名叫「mydb」,你可以使用以下的命令:
如果在這個步驟沒有產生任何回應,那就是成功了。你可以跳過本節剩餘的部份。
但你如果看到如下的訊息:
這個訊息代表 PostgreSQL 並沒有被正確的安裝。不是它沒有被安裝好,那就是你的命令路徑設定並未包含這個指令。 嘗試使用下列這個包含絕對路徑的指令看看:
命令路徑在你的系統可能會有些不同。洽詢你的系統管理員,或著檢查安裝步驟以修正這個情況。
另一種回應可能是如此:
這代表了資料庫服務尚未啓動,或者它並不存在於createdb預設連線的位置。同樣地,檢查安裝的步驟或洽詢系統管理者。
而另一種回應也可能是:
這裡指出你用來連線的使用者名稱。這種情況可能會發生在你的資料庫管理員並未建立屬於你的資料庫。(PostgreSQL 的使用者帳戶是獨立於作業系統的使用者帳戶的)如果你是資料庫管理員,請參閱,進行建立資料庫帳戶。你必須是 PostgreSQL 初始安裝的管理者(通常是 postgres),以建立第一個一般資料庫使用者的帳戶。這個情況也可能發生在,你被發配的 PostgreSQL 使用者名稱有別於你的作業系統使用者名稱,如果是這樣的話,那你需要在指令上使用 -U 選項,或者設定 PGUSER 環境變數,以指定你的 PostgreSQL 使用者名稱。
如果你有一個資料庫帳戶,但你並沒有建立資料庫的權限,你將會看到下列訊息:
並非每一個使用者都被授權可以建立一個新的資料庫。如果 PostgreSQL 拒絕你建立資料庫,那麼系統管理者就需要賦予你建立資料庫的權限。洽詢你的系統管理者,如果是這種情況的話。如果你是自行安裝 PostgreSQL,那麼你應該以你啓動資料庫服務的使用者登入作業系統,再嘗試這個操作。
你也可以建立資料庫,但使用其他的名稱。PostgreSQL 允許在資料庫系統中建立無限制數量的資料庫。資料庫名稱必須是以英文字母為開頭,總長度限制為 63 位元組。一個簡便的方式是,建立一個與你使用者名稱同名的資料庫。許多工具會預設假定資料庫名稱和你同名,所以這可以省略一些文字的輸入。要建立這樣的資料庫,只要簡單地輸入:
如果你不再使用你的資料庫,你可以移除它。舉例來說,你是 mydb 這個資料庫的擁有者(建立者),你可以使用下列指令來消毁它:
(對這個指令來說,資料庫名稱並不會預設使用你的使用者同名資料庫。你必須明確地指定名稱)這個動作會完全地移除所有和這個資料庫相關的檔案,並且沒有回復的可能,所以要進行這個動作的話,請一定要考慮清楚。
更多有關於 createdb 和 dropdb 的說明,請參閱 和 的相關章節。
版本:11
在這一章之中,提供了一個如何使用 SQL 進行簡易操作的大致概念。這裡主要讓你有基本的認識,但無法提供 SQL 完整且巨細靡遺的說明。許多書籍詳細介紹了 SQL,例如「Understanding the New SQL. A complete quide.」及「A Guide to the SQL Standard. A user's guid to the standard database language SQL.」。你應該瞭解的是,一些 PostgreSQL 語法來自於標準 SQL 的延伸。
在下面的例子當中,我們假設你已經建立了一個資料庫 mydb,如同前面章節所述,你也能夠使用 psql 了。
這些例子也放在 PostgreSQL 的原始碼之中,你可以在目錄 src/tutorial/ 下找到他們。(PostgreSQL的可執行套件可能未包含這些檔案)想要使用這些檔案的話,首先請切換到該目錄之下,然後執行 make:
這將會建立編譯 C 語言的程序,包含了使用者自訶的函式及型別。接下來,進行下列動作,以開始這個導覽:
\i 指令會去指定的檔案讀取內容,並且執行。而在 psql 的 -s 選項則可以使用單步模式執行,也就是在每一個與伺服器互動的指令之後暫停。這個指令被使用在本節的檔案 basics.sql 之中。
版本:11
到目前為止,我們的一個查詢都只涉及到一個資料表。其實可以在同一個查詢中,同時查詢多個資料表,或者在同一個資料表之中同時處理多個資料列的資料。在一個查詢之中,涉及到同一個或多個不同的資料表中的資料,稱作為交叉查詢(join)。舉個例子來說,你希望同時列出天氣和城市位置的資料。要完成這項工作,我們需要關連資料表 weather 中的 city 欄位與表格 cities 中的 name 欄位,然後回傳符合條件的資料。
這只是一個概念式的模形,交叉查詢(join)會以更有效率的方式運行,並非真正需要比較每一種組合是否符合條件,不過這些過程對於使用者而言並不會產生操作或結果上的差異。
下列查詢會產生交叉查詢的結果:
在這個結果中可以觀察到兩件事情:
不會有關於 Hayward 的結果出現。這是因為在資料表 cities 中未有 Hayward 的資料,所以交叉查詢會忽略資料表 weather 中未能關連的資料。關於這點,我們很快就會有解決辦法。
有兩個欄位顯示了城市的名稱。這樣是正確的,因為來自於資料表 weather 和 cities 的欄位被串連起來了。實務上,這樣的結果並不令人滿意,所以也許你可以明確地指出輸出的欄位,取代「 * 」的使用:
練習:試試看,當 WHERE 表示式被省略的話,查詢語句的意義會怎麼樣?
因為所有的欄位都使用不同的名稱,所以解譯器會自動發現他們所屬的資料表為何。如果在兩個資料表之中,存在有相同名稱的欄位時,你最好明確指出確定的欄位,如下所示:
多數開發者認為,在交叉查詢中,明確指出確定的欄位名稱,是良好的撰寫習慣。這樣查詢就不會因為有相同的欄位名稱而產生錯誤。而相同名稱的欄位可能是開發後續才加入的,未指明的話,就可能造成意外的結果。
交叉查詢也可以寫成如下的另一種形式:
這種語法並不如上述的常見,但我們會在這裡說明,以幫助你在後續章節的學習。
現在我們要回到前面的問題,把 Hayward 的資料放在輸出的結果之中。我們要在查詢中做的是,掃描資料表 weather,找到有所關連的每一列資料;沒有關連到的資料列,我們要填上「空值」(null)在資料表 cities 相對的欄位之中。這樣的查詢我們稱作「外部交叉查詢」(outer join)。(先前的交叉查詢為「內部交叉查詢」(inner join))。這樣的查詢指令如下所示:
這種查詢稱作為「左側外部查詢」(left outer join),因為這個交叉查詢,放在左側的資料表中的資料列,一定會在結果中至少出現一次,而右側的資料表中,則只有輸出有關連到左側資料表的資料列。當左側資料表的資料列,並沒有在右側資料表中被關連到時,屬於右側資料表的欄位就會被填上空值輸出。
練習:也有「右側外部交叉查詢」(right outer join)和「完全外部交叉查詢」(full outer join),試著找出他們都做了些什麼。
我們也可以對同一個資料表做交叉查詢,稱作為「自我交叉查詢」(self join)。接下來的範例,假設我們希望找到所有氣溫範圍的天氣資料。所以我們需要讓 temp_lo 及 temp_hi 兩個欄位,和其他的 temp_lo 及 temp_high 相比較。我們可以用下列的查詢來符合需求:
這裡我們重新命名了資料表 weather 為 W1 及 W2,以在交叉查詢中區分左側及右側。你也可以在其他查詢中使用這個技巧,以節省輸入的複雜度,例如:
你將會在後續內容中,不斷練習到這樣的使用方式。
版本:11
如同其他的關連式資料庫產品,PostgreSQL 也支援彙總查詢的功能。彙總查詢指的是能夠把多個資料列的資料經過計算,產生單一結果的功能。舉例來說, count、sum、avg(平均值)、max(最大值)、min(最小值)都是彙總查詢的函式。
這裡的例子,我們可以得到所有低溫中的最大值:
如果我們想要知道,這個數值是發生在哪一個城市?也許可以試試:
不過,這行不通,因為 max 不能使用在 WHERE 條件式當中。(會有這樣的限制,是因為 WHERE 條件式目的是要判斷有哪些資料列的資料應該被彙總計算,所以很明顯地,這件事必須要在彙整計算前發生,這就產生了矛盾。)所以,像本例的查詢一般會使用子查詢(subquery)來取得適當的結果:
這樣就對了,因為子查詢是一個獨立的查詢,它可以獨立進行彙總查詢,有別於括號以外的查詢語句。
彙總查詢和 GROUP BY 一起使用會很方便的。舉例來說,我們可以得到每個城市所觀測到的最高氣溫:
這個查詢對每個城市都輸出一列的結果。每一個彙總的結果,將整個資料表,以關連到的城市進行計算。 而我們可以進一步過濾資料內容,使用 HAVING:
如果限制所有 temp_lo 的數值必須要小於 40 (WHERE temp_lo < 40)的話,也可能得到相同的結果。 最後,如果我們只關心以"S"開頭的城市的話,可以這樣做:
LIKE 運算子進行特徵比對運算,這將會在 9.7. 特徵比對中進一步說明。
這裡很重要的是,瞭解 SQL 中 WHERE 和 HAVING 之間的行為。其根本上的差異是:WHERE 會在合併和彙總計算之前進行選擇資料的動作(也就是它控制著,哪些資料需要被彙總計算);而 HAVING 是在合併及彙整計算之後,才進行過濾資料的動作。所以,在 WHERE 條件式當中,絕不可以使用彙整運算式;另一方面,HAVING 條件式總是使用彙整運算式。(嚴格來說,你也可以不在 HAVING 條件式中使用彙整運算式,但很少人這樣使用,通常就會改寫到 WHERE 條式件當中,那會更有效率。)
在先前的例子當中,我們可以把城市名稱的限制放在 WHERE 條件式之中,因為它不需要彙總。這將會比放在 HAVING 條件式中更有效率,因為這樣可以避免合併及彙整運算整個表格,不用浪費時間在本來就會被過濾掉的資料上。
版本:11
你可以創建一個新的資料表,為它取一個名字,並且宣告所有的欄位名稱與其資料型別:
你可以把上述內容在 psql 中輸入,包含換行字元不會影響判讀。psql 是以分號作為指令結束的判定。
空白(包含「空白」、「定位符號」和「換行符號」)都可以自由使用在 SQL 指令當中。這表示你可以將指令以不同的形式排版,甚至全部寫都在一行也沒問題。使用破折號,連續2個("--"),表示緊接的內容只是註解,直到該行結束為止。PostgreSQL 是不分大小寫字母的,包括各類關鍵字和描述語,除非是使用雙引號括起來的文字。(更精確地說,沒有被雙引號括起來的識別字,都會轉為小寫字母進行識別)
varchar(80) 表示指定一個資料型別,它可以儲放任意 80 個字元以內的字串。int 是一般認知的整數型別。real 表示資料是單精確度的浮點數。date 顧名思義,就是日期時間型別。(本例中欄位名稱和型別都使用 date,這可能是方便,也可能是困擾,端看你如何使用。)
PostgreSQL 支援標準的資料型別 int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, interval,也支援了複合型的地理資料型別。PostgreSQL 可以自訂組合任意數量的資料型別。語法上,資料型別名稱並不是保留關鍵字的範圍,除非特定的標準 SQL 支援需求之外。
第二個例子用來儲存城市及其所在的地理位置:
point 型別是一個 PostgreSQL專屬資料型別的範例。
最後,應該被點出來的是,如果你不再需要一個表格,或者想要重新以別的方式創建它,那麼你可以以下列的指令來移除它:
版本:11
INSERT 指令被用來將資料以資料列(row)的形式,新增至資料表(table)之中:
注意,所有的資料型別都有明確的輸入格式。只要不是簡單的數值內容,都必須要以單引號(')括住,如同在本例中的形式。日期時間型別(date type)的資料內容就比較有彈性,但在這個導覽之中,我們仍然使用較固定的格式來表現。
地理資訊型別(point type)需要有座標組作為輸入,如下所示:
到目前為止,語法的使用需要你依照欄位宣告的次序擺放,而另一種語法可以允許你明確地指定資料相對應的欄位:
你可以將欄位以不同的次序擺放,甚或略去某些欄位,例如,precipitation 欄位(prcp)內容未知:
許多開發者會認為,在撰寫習慣上,明確指定欄位是比較好的方式。
請執行下列的指令,你將會擁有後續章節所需要的範例資料。
你可能需要使用 COPY 這個指令從文字檔案來載入大量的資料。這個指令會比 INSERT 要快上許多,因為 COPY 指令的設計就是為了大量資料輸入而產生的。它少了一些彈性,但提供了效率上的最佳表現。使用範例如下所示:
資料來源的檔案必須存在於後端的伺服器之中,並且可被 PostgreSQL 使用者(postgres)所存取,注意不是用戶端的主機,因為後端伺服器的服務需要直接讀取該檔案。你可以取得更多詳細說明,在 COPY 指令的說明頁面。
除了本文件之外,PostgreSQL還有其他的參考資訊:
PostgreSQL的wiki記錄了常見的問題,正準備進行的工作,以及其他更多不同主題的資訊。
PostgreSQL wiki 也有台灣中文的頁面喔。
PostgreSQL的官方網站,有最新軟體的釋出訊息,讓你能夠和PostgreSQL相處得更棒!
郵件列表的功能,是一個為您解答疑問的好地方,你也可以分享使用經驗給其他同好,或直接和開發者溝通。 詳情請參閱PostgreSQL的官方網站。
PostgreSQL是一個開源的專案,也就是說,它仰賴社群的每一個人給予支持。當你開始使用PostgreSQL,你會需要其他人的幫助,可能是透過文件或是郵件列表的功能。請考慮也可以回饋您的知識。在閱讀郵件列表和回答疑問的同時,如果你學到了未被文件記載的知識時,請寫下來,並且供獻出來。如果你撰寫了一些程式碼增加了特別的功能,也希望能夠回饋到社群之中。
版本:11
要從資料表(table)中取出資料,稱作資料表的查詢。要進行這個行為,你需要 SQL 中的 SELECT 指令。這個指令由幾個部份所組成,回傳列表(select list,想要回傳的欄位)、資料表列表(資料來源的資料表)、選擇性的條件定義(指定一些限制條件)。舉個例子來說,要取得資料表 weather 中所有的資料的話,請輸入:
這裡的星號 * 表示「所有欄位」。下列的指令會回傳相同的結果。
其輸出結果將會如下所示:
你可以在回傳列表中撰寫一些運算表示式,而不只是簡單的欄位引用。舉例來說,你可以輸入:
這應該會產生這樣的結果:
注意,「AS」被用來重新命名輸出的欄位。(選用)
查詢語句可以加上「WHERE」來設定限制條件,以指定哪些列才需要被回傳。WHERE 的內容是一個布林(truth value)表示式,而只有在其運算值為真(true)時,該列才會被回傳。一般的布林運算子(AND, OR, NOT)都是被允許出現在表示式中的。舉例來說,下列的指令將會回傳 San Francisco 在雨天的天氣數值:
結果:
你可以將結果進行排序:
在這個例子之中,其次序並沒有完全地被指定,所以你可能會得到 San Francisco 的列以另一種次序呈現。而你如果以下列指令查詢的話,那你就會得到如上但固定的結果:
你可以在查詢時去除重覆的列:
再一次,其結果的次序可能每次都不同,你可以同時使用 DISTINCT 及 ORDER BY 來確保能得到一致性的查詢結果:
回想一下在中的表格 weather 及 cities,思考下列問題: 你想要保證沒有另一個人可以新增在 cities 中沒有的城市資料到 weather 中。這就是所謂資料關連性的管理。在簡單的資料庫系統當中,可能會這樣實作:先檢查 cities 中是否已有對應的資料,然後再決定資料表 weather 中新增或拒絕新的天氣資料。這個辦法還有很多問題,而且很不方便,所以 PostgreSQL 可以幫助你解決這個需求。
新的資料表宣告如下所示:
現在嘗試新增一筆不合理的資料:
外部索引鍵或簡稱外部鍵(foreign key)的行為可以讓你的應用程式變得容易調整。我們在這個導覽中不會在深入這個簡單的例子了,但你可以在取得進一步的資訊。正確地使用外部索引鍵,可以改善資料庫應用程式的品質,所以強烈建議一定要好好學習它。
版本:11
要把某些資料列從資料表中移除,就使用 DELETE 這個指令。假設你對於 Hayward 這個城市的天氣不再感興趣了,那麼你可以執行下列指令,來刪除資料表中的這些資料:
所有關於 Hayward 的資料都被刪除了。
這個指令有一個應該要特別注意的情況:
沒有任何限制的條件,DELETE 將會刪去所有該資料表中的資料,使成為空的資料表。資料庫系統並不會在這個動作執行前和你確認!
讓我們回到 2.6 節的查詢範例。假設關連天氣資訊和城市位置的結果,是你的應用中特別常用的,但你並不想要每次都要輸入一長串的查詢語句。那麼,你可以為這個查詢語句建立一個「檢視表(View)」,你可以取一個名字,當你需要使用的時候,你可以把它當作一個資料表來使用:
妥善地使用檢視表,對於良好的 SQL 資料庫設計而言,是很關鍵的部份。檢視表允許你可封裝你的資料表結構與細節,當你的應用系統在逐步發展成熟的過程中,扮演一致性的資料介面。
檢視表可以用在大多數資料表可以使用的地方。而用檢視表來封裝其他檢視表的情況,也不少見。
PostgreSQL 還有許多這份導覽中未能介紹到的功能,這裡主要是針對新鮮的 SQL 使用者所準備的內容。 這些功能將會在後續的章節進行更詳細的討論。
如果你覺得你需要更多介紹的資訊,可以到 PostgreSQL 的官方網站取得更多訊息。
在這個部份介紹如何在 PostgreSQL 中使用 SQL 語言。首先,我們從一般性的 SQL 語法開始說明,然後解釋如何建立結構來保存資料,如何充實資料庫,以及如何查詢資料的方法。中段的部份列出 SQL 指令中的資料型別與函數。最後剩餘的部份,將會針對一些調教資料庫的重要議題進行說明。
這個部份的內容設計讓初學者可以循序漸進地完整瞭解該主題,而不需要反覆前後查閱。各章的內容設計上都是獨立的,所以進階的使用者可以分別閱讀他們需要的部份。在這個部份的內容,針對於主題式的單元描述。需要瞭解詳情的讀者,請參閱第 6 部份中,個別指令的說明頁面。
在這個部份裡的讀者,應該要知道如何連線到一個 PostgreSQL 資料庫,並且執行 SQL 指令。如果不熟悉這些操作的讀者,建議先閱讀第 1 部份的內容。SQL 指令一般是使用終端工具 psql,但其他具有類似功能的程式也可以使用。
版本:11
你可以使用 UPDATE 指令以列為單位來更新資料。假設你發現氣溫的數值測量在 11 月 28 日之後都多了 2 度。你可以以下列語法來修正這些資料:
查看一下這些更新後的資料:
這章中說明 SQL 的使用語法。從這裡建立後續章節所需的理解基礎,然後進一步瞭解 SQL 如何使用去定義及修改資料。
我們也建議已經熟悉 SQL 語法的使用者,仔細地閱讀本章,因為這裡包含了一些有別於其他 SQL 資料庫或專屬於 PostgreSQL 的規則和觀念。