PostgreSQL 正體中文使用手冊
PostgreSQL.TW官方使用手冊小島故事加入社團
14
14
  • 簡介
  • 前言
    • 1. 什麼是 PostgreSQL?
    • 2. PostgreSQL 沿革
    • 3. 慣例
    • 4. 其他參考資訊
    • 5. 問題回報指南
  • I. 新手教學
    • 1. 入門指南
      • 1.1. 安裝
      • 1.2. 基礎架構
      • 1.3. 建立一個資料庫
      • 1.4. 存取一個資料庫
    • 2. SQL 查詢語言
      • 2.1. 簡介
      • 2.2. 概念
      • 2.3. 創建一個新的資料表
      • 2.4. 資料列是資料表的組成單位
      • 2.5. 資料表的查詢
      • 2.6. 交叉查詢
      • 2.7. 彙總查詢
      • 2.8. 更新資料
      • 2.9. 刪除資料
    • 3. 先進功能
      • 3.1. 簡介
      • 3.2. 檢視表(View)
      • 3.3. 外部索引鍵
      • 3.4. 交易安全
      • 3.5. 窗函數
      • 3.6. 繼承
      • 3.7. 結論
  • II. SQL 查詢語言
    • 4. SQL 語法
      • 4.1. 語法結構
      • 4.2. 參數表示式
      • 4.3. 函數呼叫
    • 5. 定義資料結構
      • 5.1. 認識資料表
      • 5.2. 預設值
      • 5.3. Generated Columns
      • 5.4. 限制條件
      • 5.5. 系統欄位
      • 5.6. 表格變更
      • 5.7. 權限
      • 5.8. 資料列安全原則
      • 5.9. Schemas
      • 5.10. 繼承
      • 5.11. 分割資料表
      • 5.12. 外部資料
      • 5.13. 其他資料庫物件
      • 5.14. 相依性追蹤
    • 6. 資料處理
      • 6.1. 新增資料
      • 6.2. 更新資料
      • 6.3. 刪除資料
      • 6.4. 修改並回傳資料
    • 7. 資料查詢
      • 7.1. 概觀
      • 7.2. 資料表表示式
      • 7.3. 取得資料列表
      • 7.4. 合併查詢結果
      • 7.5. 資料排序
      • 7.6. LIMIT 和 OFFSET
      • 7.7. VALUES 列舉資料
      • 7.8. WITH Querys(Common Table Expressions)
    • 8. 資料型別
      • 8.1. 數字型別
      • 8.2. 貨幣型別
      • 8.3. 字串型別
      • 8.4. 位元組型別(bytea)
      • 8.5. 日期時間型別
      • 8.6. 布林型別
      • 8.7. 列舉型別
      • 8.8. 地理資訊型別
      • 8.9. 網路資訊型別
      • 8.10. 位元字串型別
      • 8.11. 全文檢索型別
      • 8.12. UUID 型別
      • 8.13. XML 型別
      • 8.14. JSON 型別
      • 8.15. 陣列
      • 8.16. 複合型別
      • 8.17. 範圍型別
      • 8.18. Domain Types
      • 8.19. 物件指標型別
      • 8.20. pg_lsn 型別
      • 8.21. 概念型別
    • 9. 函式及運算子
      • 9.1. 邏輯運算子
      • 9.2. 比較函式及運算子
      • 9.3. 數學函式及運算子
      • 9.4. 字串函式及運算子
      • 9.5. 位元字串函式及運算子
      • 9.6. 二元字串函式及運算子
      • 9.7. 特徵比對
      • 9.8. 型別轉換函式
      • 9.9 日期時間函式及運算子
      • 9.10. 列舉型別函式
      • 9.11. 地理資訊函式及運算子
      • 9.12. 網路位址函式及運算子
      • 9.13. 文字檢索函式及運算子
      • 9.14. UUID Functions
      • 9.15. XML 函式
      • 9.16. JSON 函式及運算子
      • 9.17. 序列函式
      • 9.18. 條件表示式
      • 9.19. 陣列函式及運算子
      • 9.20. 範圍函式及運算子
      • 9.21. 彙總函數
      • 9.22. Window 函式
      • 9.23. 子查詢
      • 9.24. 資料列與陣列的比較運算
      • 9.25. 集合回傳函式
      • 9.26. 系統資訊函數
      • 9.27. 系統管理函式
      • 9.28. 觸發函式
      • 9.29. 事件觸發函式
      • 9.30. Statistics Information Functions
    • 10. 型別轉換
      • 10.1. 概觀
      • 10.2. 運算子
      • 10.3. 函式
      • 10.4. 資料儲存轉換規則
      • 10.5. UNION、CASE 等相關結構
      • 10.6. SELECT 輸出規則
    • 11. 索引(Index)
      • 11.1. 簡介
      • 11.2. 索引型別
      • 11.3. 多欄位索引
      • 11.4. 索引與 ORDER BY
      • 11.5. 善用多個索引
      • 11.6. 唯一值索引
      • 11.7. 表示式索引
      • 11.8. 部份索引(partial index)
      • 11.9. Index-Only Scans and Covering Indexes
      • 11.10. 運算子物件及家族
      • 11.11. 索引與排序規則
      • 11.12. 檢查索引運用
    • 12. 全文檢索
      • 12.1. 簡介
      • 12.2. 查詢與索引
      • 12.3. 細部控制
      • 12.4. 延伸功能
      • 12.5. 斷詞
      • 12.6. 字典
      • 12.7. 組態範例
      • 12.8. 測試與除錯
      • 12.9. GIN 及 GiST 索引型別
      • 12.10. psql支援
      • 12.11. 功能限制
    • 13. 一致性管理(MVCC)
      • 13.1. 簡介
      • 13.2. 交易隔離
      • 13.3. 鎖定模式
      • 13.4. 在應用端檢視資料一致性
      • 13.5. 特別注意
      • 13.6. 鎖定與索引
    • 14. 效能技巧
      • 14.1. 善用 EXPLAIN
      • 14.2. 統計資訊
      • 14.3. 使用確切的 JOIN 方式
      • 14.4. 快速建立資料庫內容
      • 14.5. 風險性彈性設定
    • 15. 平行查詢
      • 15.1. 如何運作?
      • 15.2. 啓用時機?
      • 15.3. 平行查詢計畫
      • 15.4. 平行查詢的安全性
  • III. 系統管理
    • 16. Installation from Binaries
    • 17. 用原始碼安裝
      • 16.1. Short Version
      • 16.2. Requirements
      • 16.3. Getting The Source
      • 16.4. 安裝流程
      • 16.5. Post-Installation Setup
      • 16.6. Supported Platforms
      • 16.7. 平台相關的注意事項
    • 18. 用原始碼在 Windows 上安裝
      • 17.1. Building with Visual C++ or the Microsoft Windows SDK
    • 19. 服務配置與維運
      • 18.1. PostgreSQL 使用者帳號
      • 18.2. Creating a Database Cluster
      • 18.3. Starting the Database Server
      • 18.4. 核心資源管理
      • 18.5. Shutting Down the Server
      • 18.6. Upgrading a PostgreSQL Cluster
      • 18.7. Preventing Server Spoofing
      • 18.8. Encryption Options
      • 18.9. Secure TCP/IP Connections with SSL
      • 18.10. Secure TCP/IP Connections with GSSAPI Encryption
      • 18.11. Secure TCP/IP Connections with SSH Tunnels
      • 18.12. 在 Windows 註冊事件日誌
    • 20. 服務組態設定
      • 19.1. Setting Parameters
      • 19.2. File Locations
      • 19.3. 連線與認證
      • 19.4. 資源配置
      • 19.5. Write Ahead Log
      • 19.6. 複寫(Replication)
      • 19.7. 查詢規畫
      • 19.8. 錯誤回報與日誌記錄
      • 19.9. 執行階段統計資訊
      • 19.10. 自動資料庫清理
      • 20.11. 用戶端連線預設參數
      • 19.12. 交易鎖定管理
      • 19.13. 版本與平台的相容性
      • 19.14. Error Handling
      • 19.15. 預先配置的參數
      • 19.16. Customized Options
      • 19.17. Developer Options
      • 19.18. Short Options
    • 21. 使用者認證
      • 20.1. 設定檔:pg_hba.conf
      • 20.2. User Name Maps
      • 20.3. Authentication Methods
      • 20.4. Trust Authentication
      • 20.5. Password Authentication
      • 20.6. GSSAPI Authentication
      • 20.7. SSPI Authentication
      • 20.8. Ident Authentication
      • 20.9. Peer Authentication
      • 20.10. LDAP Authentication
      • 20.11. RADIUS Authentication
      • 20.12. Certificate Authentication
      • 20.13. PAM Authentication
    • 22. 資料庫角色
      • 22.1. Database Roles
      • 22.2. Role Attributes
      • 22.3. Role Membership
      • 22.4. 移除角色
      • 22.5. Default Roles
      • 22.6. Function Security
    • 23. Managing Databases
      • 22.1. Overview
      • 22.2. Creating a Database
      • 22.3. 樣版資料庫
      • 22.4. Database Configuration
      • 22.5. Destroying a Database
      • 22.6. Tablespaces
    • 24. 語系
      • 23.1. 語系支援
      • 23.2. Collation Support
      • 23.3. 字元集支援
    • 25. 例行性資料庫維護工作
      • 25.1. 例行性資料清理
      • 25.2. 定期重建索引
      • 25.3. Log 檔案維護
    • 26. 備份及還原
      • 25.1. SQL Dump
      • 25.2. 檔案系統層級備份
      • 25.3. Continuous Archiving and Point-in-Time Recovery (PITR)
    • 27. High Availability, Load Balancing, and Replication
      • 26.1. 比較不同的解決方案
      • 26.2. 日誌轉送備用伺服器 Log-Shipping Standby Servers
      • 26.3. Failover
      • 26.4. Alternative Method for Log Shipping
      • 26.5. Hot Standby
    • 28. 監控資料庫活動
      • 27.1. Standard Unix Tools
      • 27.2. 統計資訊收集器
      • 27.3. Viewing Locks
      • 27.4. Progress Reporting
      • 27.5. Dynamic Tracing
    • 29. 監控磁碟使用情況
      • 28.1. 瞭解磁碟使用情形
      • 28.2. 磁碟空間不足錯誤
    • 30. 高可靠度及預寫日誌
      • 29.1. 可靠度
      • 29.2. Write-Ahead Logging(WAL)
      • 29.3. Asynchronous Commit
      • 29.4. WAL Configuration
      • 29.5. WAL Internals
    • 31. 邏輯複寫(Logical Replication)
      • 30.1. 發佈(Publication)
      • 30.2. 訂閱(Subscription)
      • 30.3. 衝突處理
      • 30.4. 限制
      • 30.5. 架構
      • 30.6. 監控
      • 30.7. 安全性
      • 30.8. 系統設定
      • 30.9. 快速設定
    • 32. Just-in-Time Compilation(JIT)
      • 31.1. What is JIT compilation?
      • 31.2. When to JIT?
      • 31.3. Configuration
      • 31.4. Extensibility
    • 33. 迴歸測試
      • 32.1. Running the Tests
      • 32.2. Test Evaluation
      • 32.3. Variant Comparison Files
      • 32.4. TAP Tests
      • 32.5. Test Coverage Examination
  • IV. 用戶端介面
    • 33. libpq - C Library
      • 33.1. 資料庫連線控制函數
      • 33.2. 連線狀態函數
      • 33.3. Command Execution Functions
      • 33.4. Asynchronous Command Processing
      • 33.5. Retrieving Query Results Row-By-Row
      • 33.6. Canceling Queries in Progress
      • 33.7. The Fast-Path Interface
      • 33.8. Asynchronous Notification
      • 33.9. Functions Associated with the COPY Command
      • 33.10. Control Functions
      • 33.11. Miscellaneous Functions
      • 33.12. Notice Processing
      • 33.13. Event System
      • 33.14. 環境變數
      • 33.15. 密碼檔
      • 33.16. The Connection Service File
      • 33.17. LDAP Lookup of Connection Parameters
      • 33.18. SSL Support
      • 33.19. Behavior in Threaded Programs
      • 33.20. Building libpq Programs
      • 33.21. Example Programs
    • 34. Large Objects
      • 35.1. Introduction
      • 35.2. Implementation Features
      • 35.3. Client Interfaces
      • 35.4. Server-side Functions
      • 35.5. Example Program
    • 35. ECPG - Embedded SQL in C
      • 35.1. The Concept
      • 35.2. Managing Database Connections
      • 35.3. Running SQL Commands
      • 35.4. Using Host Variables
      • 35.5. Dynamic SQL
      • 35.6. pgtypes Library
      • 35.7. Using Descriptor Areas
      • 35.8. Error Handling
      • 35.9. Preprocessor Directives
      • 35.10. Processing Embedded SQL Programs
      • 35.11. Library Functions
      • 35.12. Large Objects
      • 35.13. C++ Applications
      • 35.14. Embedded SQL Commands
      • 35.15. Informix Compatibility Mode
      • 35.16. Internals
    • 36. The Information Schema
      • 36.1. The Schema
      • 36.2. Data Types
      • 36.3. information_schema_catalog_name
      • 36.4. administrable_role_authorizations
      • 36.5. applicable_roles
      • 36.6. attributes
      • 36.7. character_sets
      • 36.8. check_constraint_routine_usage
      • 36.9. check_constraints
      • 36.10. collations
      • 36.11. collation_character_set_applicability
      • 36.12. column_domain_usage
      • 36.13. column_options
      • 36.14. column_privileges
      • 36.16. column_udt_usage
      • 36.17. columns
      • 36.18. constraint_column_usage
      • 37.18. constraint_table_usage
      • 37.19. data_type_privileges
      • 37.20. domain_constraints
      • 37.21. domain_udt_usage
      • 37.22. domains
      • 37.23. element_types
      • 37.24. enabled_roles
      • 37.25. foreign_data_wrapper_options
      • 37.26. foreign_data_wrappers
      • 37.27. foreign_server_options
      • 37.28. foreign_servers
      • 37.29. foreign_table_options
      • 37.30. foreign_tables
      • 36.32. key_column_usage
      • 36.33. parameters
      • 36.34. referential_constraints
      • 37.34. role_column_grants
      • 37.35. role_routine_grants
      • 36.37. role_table_grants
      • 37.37. role_udt_grants
      • 37.38. role_usage_grants
      • 37.39. routine_privileges
      • 37.40. routines
      • 36.42. schemata
      • 37.42. sequences
      • 37.43. sql_features
      • 37.44. sql_implementation_info
      • 37.45. sql_languages
      • 37.46. sql_packages
      • 37.47. sql_parts
      • 37.48. sql_sizing
      • 37.49. sql_sizing_profiles
      • 36.51. table_constraints
      • 36.49. table_privileges
      • 37.52. tables
      • 37.53. transforms
      • 37.54. triggered_update_columns
      • 37.55. triggers
      • 37.56. udt_privileges
      • 37.57. usage_privileges
      • 37.58. user_defined_types
      • 37.59. user_mapping_options
      • 37.60. user_mappings
      • 37.61. view_column_usage
      • 37.62. view_routine_usage
      • 37.63. view_table_usage
      • 37.64. views
  • V. 資料庫程式設計
    • 38. SQL 延伸功能
      • 38.1. How Extensibility Works
      • 37.2. The PostgreSQL Type System
      • 37.3. 使用者自訂函數
      • 37.4. User-defined Procedures
      • 37.5. Query Language (SQL) Functions
      • 37.6. Function Overloading
      • 37.7. 函數易變性類別
      • 37.8. Procedural Language Functions
      • 37.9. Internal Functions
      • 37.10. C-Language Functions
      • 37.11. Function Optimization Information
      • 37.12. User-defined Aggregates
      • 37.13. User-defined Types
      • 37.14. User-defined Operators
      • 37.15. Operator Optimization Information
      • 38.16. Interfacing Extensions To Indexes
      • 37.17. 封裝相關物件到延伸功能中
      • 37.18. Extension Building Infrastructure
    • 38. Triggers
      • 38.1. Overview of Trigger Behavior
      • 38.2. Visibility of Data Changes
      • 38.3. Writing Trigger Functions in C
      • 38.4. A Complete Trigger Example
    • 39. Event Triggers (事件觸發)
      • 39.1. Overview of Event Trigger Behavior
      • 39.2. Event Trigger Firing Matrix
      • 39.3. Writing Event Trigger Functions in C
      • 39.4. A Complete Event Trigger Example
    • 40. 規則系統
      • 40.1. The Query Tree
      • 40.2. Views and the Rule System
      • 40.3. Materialized Views
      • 40.4. Rules on INSERT, UPDATE, and DELETE
      • 40.5. 規則及權限
      • 40.6. Rules and Command Status
      • 40.7. Rules Versus Triggers
    • 41. Procedural Languages(程序語言)
      • 41.1. Installing Procedural Languages
      • 41.2. Structure of PL/pgSQL
      • 41.5. Basic Statements
      • 41.11. 深入了解 PL/pgSQL
    • 42. PL/pgSQL - SQL Procedural Language
      • 42.1. Overview
      • 42.2. Structure of PL/pgSQL
      • 42.3. Declarations
      • 42.4. Expressions
      • 42.5. 基本語法
      • 42.6. Control Structures
    • 43. PL/Tcl - Tcl Procedural Language
    • 44. PL/Perl — Perl Procedural Language
    • 45. PL/Python - Python Procedural Language
      • 45.1. Python 2 vs. Python 3
      • 45.2. PL/Python Functions
      • 45.3. Data Values
      • 45.4. Sharing Data
      • 45.5. Anonymous Code Blocks
      • 45.6. Trigger Functions
      • 45.7. Database Access
      • 45.8. Explicit Subtransactions
      • 45.9. Transaction Management
      • 45.10. Utility Functions
      • 45.11. Environment Variables
    • 46. Server Programming Interface
    • 47. Background Worker Processes
    • 48. Logical Decoding
      • 48.1. Logical Decoding Examples
      • 48.2. Logical Decoding Concepts
      • 48.3. Streaming Replication Protocol Interface
      • 48.4. Logical Decoding SQL Interface
      • 48.5. System Catalogs Related to Logical Decoding
      • 48.6. Logical Decoding Output Plugins
      • 48.7. Logical Decoding Output Writers
      • 48.8. Synchronous Replication Support for Logical Decoding
    • 49. Replication Progress Tracking
  • VI. 參考資訊
    • I. SQL 指令
      • ALTER DATABASE
      • ALTER DEFAULT PRIVILEGES
      • ALTER EXTENSION
      • ALTER FUNCTION
      • ALTER INDEX
      • ALTER LANGUAGE
      • ALTER MATERIALIZED VIEW
      • ALTER POLICY
      • ALTER PUBLICATION
      • ALTER ROLE
      • ALTER RULE
      • ALTER SCHEMA
      • ALTER SEQUENCE
      • ALTER STATISTICS
      • ALTER SUBSCRIPTION
      • ALTER SYSTEM
      • ALTER TABLE
      • ALTER TABLESPACE
      • ALTER TRIGGER
      • ALTER TYPE
      • ALTER USER
      • ALTER VIEW
      • ANALYZE
      • CLUSTER
      • COMMENT
      • COMMIT PREPARED
      • COPY
      • CREATE ACCESS METHOD
      • CREATE CAST
      • CREATE DATABASE
      • CREATE EVENT TRIGGER
      • CREATE EXTENSION
      • CREATE FOREIGN TABLE
      • CREATE FOREIGN DATA WRAPPER
      • CREATE FUNCTION
      • CREATE INDEX
      • CREATE LANGUAGE
      • CREATE MATERIALIZED VIEW
      • CREATE DOMAIN
      • CREATE POLICY
      • CREATE PROCEDURE
      • CREATE PUBLICATION
      • CREATE ROLE
      • CREATE RULE
      • CREATE SCHEMA
      • CREATE SEQUENCE
      • CREATE SERVER
      • CREATE STATISTICS
      • CREATE SUBSCRIPTION
      • CREATE TABLE
      • CREATE TABLE AS
      • CREATE TABLESPACE
      • CREATE TRANSFORM
      • CREATE TRIGGER
      • CREATE TYPE
      • CREATE USER
      • CREATE USER MAPPING
      • CREATE VIEW
      • DEALLOCATE
      • DELETE
      • DO
      • DROP ACCESS METHOD
      • DROP DATABASE
      • DROP EXTENSION
      • DROP FUNCTION
      • DROP INDEX
      • DROP LANGUAGE
      • DROP MATERIALIZED VIEW
      • DROP OWNED
      • DROP POLICY
      • DROP PUBLICATION
      • DROP ROLE
      • DROP RULE
      • DROP SCHEMA
      • DROP SEQUENCE
      • DROP STATISTICS
      • DROP SUBSCRIPTION
      • DROP TABLE
      • DROP TABLESPACE
      • DROP TRANSFORM
      • DROP TRIGGER
      • DROP TYPE
      • DROP USER
      • DROP VIEW
      • EXECUTE
      • EXPLAIN
      • GRANT
      • IMPORT FOREIGN SCHEMA
      • INSERT
      • LISTEN
      • LOAD
      • NOTIFY
      • PREPARE
      • PREPARE TRANSACTION
      • REASSIGN OWNED
      • REFRESH MATERIALIZED VIEW
      • REINDEX
      • RESET
      • REVOKE
      • ROLLBACK PREPARED
      • SECURITY LABEL
      • SELECT
      • SELECT INTO
      • SET
      • SET CONSTRAINTS
      • SET ROLE
      • SET SESSION AUTHORIZATION
      • SET TRANSACTION
      • SHOW
      • TRUNCATE
      • UNLISTEN
      • UPDATE
      • VACUUM
      • VALUES
    • II. PostgreSQL 用戶端工具
      • createdb
      • createuser
      • dropdb
      • dropuser
      • oid2name
      • pgbench
      • pg_basebackup
      • pg_dump
      • pg_dumpall
      • pg_isready
      • pg_receivewal
      • pg_recvlogical
      • pg_restore
      • pg_verifybackup
      • psql
      • vacuumdb
    • III. PostgreSQL 伺服器應用程式
      • initdb
      • pg_archivecleanup
      • pg_ctl
      • pg_standby
      • pg_test_timing
      • pg_upgrade
      • postgres
  • VII. 資料庫進階
    • 50. PostgreSQL 的內部架構
      • 50.1. 處理查詢語句的流程
      • 50.2. How Connections Are Established
      • 50.3. The Parser Stage
      • 50.4. The PostgreSQL Rule System
      • 50.5. Planner/Optimizer
      • 50.6. Executor
    • 51. 系統目錄
      • 51.3. pg_am
      • 51.7. pg_attribute
      • 51.8. pg_authid
      • 51.9. pg_auth_members
      • 51.10. pg_cast
      • 51.11 pg_class
      • 51.12. pg_collation
      • 51.13. pg_constraint
      • 51.15 pg_database
      • 51.21. pg_event_trigger
      • 51.22. pg_extension
      • 51.26 pg_index
      • 51.29. pg_language
      • 51.32. pg_namespace
      • 51.33. pg_opclass
      • 51.38. pg_policy
      • 51.39. pg_proc
      • 51.44. pg_rewrite
      • 51.49. pg_statistic
      • 51.50. pg_statistic_ext
      • 51.52. pg_subscription
      • 51.53. pg_subscription_rel
      • 51.54. pg_tablespace
      • 51.56. pg_trigger
      • 51.62. pg_type
      • 51.66. pg_available_extensions
      • 51.67. pg_available_extension_versions
      • 51.71. pg_hba_file_rules
      • 51.72. pg_indexes
      • 51.73. pg_locks
      • 51.77. pg_prepared_xacts
      • 51.79. pg_replication_origin_status
      • 51.80. pg_replication_slots
      • 51.82 pg_roles
      • 51.85. pg_settings
      • 51.87. pg_shmem_allocations
      • 51.88. pg_stats
      • 51.90. pg_tables
      • 51.93. pg_user
      • 51.95. pg_views
    • 52. Frontend/Backend Protocol
      • 52.1. Overview
      • 52.2. Message Flow
      • 52.3. SASL Authentication
      • 52.4. Streaming Replication Protocol
      • 52.5. Logical Streaming Replication Protocol
      • 52.6. Message Data Types
      • 52.7. Message Formats
      • 52.8. Error and Notice Message Fields
      • 52.9. Logical Replication Message Formats
      • 52.10. Summary of Changes since Protocol 2.0
    • 53. PostgreSQL 程式撰寫慣例
      • 53.1. Formatting
      • 53.2. Reporting Errors Within the Server
      • 53.3. Error Message Style Guide
      • 53.4. Miscellaneous Coding Conventions
    • 54. Native Language Support
      • 54.1. For the Translator
      • 54.2. For the Programmer
    • 55. 撰寫程序語言的處理程序
    • 56. Writing a Foreign Data Wrapper
      • 56.1. Foreign Data Wrapper Functions
      • 56.2. Foreign Data Wrapper Callback Routines
      • 56.3. Foreign Data Wrapper Helper Functions
      • 56.4. Foreign Data Wrapper Query Planning
      • 56.5. Row Locking in Foreign Data Wrappers
    • 59. Genetic Query Optimizer
      • 59.1. Query Handling as a Complex Optimization Problem
      • 59.2. Genetic Algorithms
      • 59.3. Genetic Query Optimization (GEQO) in PostgreSQL
      • 59.4. Further Reading
    • 60. Table Access Method Interface Definition
    • 61. Index Access Method Interface Definition
    • 62. Generic WAL Records
    • 64. B-Tree Indexes
      • 64.1. Introduction
      • 64.2. Behavior of B-Tree Operator Classes
      • 64.3. B-Tree Support Functions
      • 64.4. Implementation
    • 64. GiST Indexes
      • 64.1. Introduction
      • 64.2. Built-in Operator Classes
      • 64.3. Extensibility
      • 64.4. Implementation
      • 64.5. Examples
    • 65. SP-GiST Indexes
      • 65.1. Introduction
      • 65.2. Built-in Operator Classes
      • 65.3. Extensibility
      • 65.4. Implementation
      • 65.5. Examples
    • 66. GIN 索引
      • 66.1. 簡介
      • 66.2. 內建運算子類
      • 66.3. 延伸介面
      • 66.4. 實作說明
      • 66.5. GIN 小巧技
      • 66.6. 限制
      • 66.7. 範例
    • 67. BRIN Indexes
      • 67.1. Introduction
      • 67.2. Built-in Operator Classes
      • 67.3. Extensibility
    • 68. 資料庫實體儲存格式
      • 68.1. Database File Layout
      • 68.2. TOAST
      • 68.3. Free Space Map
      • 68.4 可視性映射表(Visibility Map)
      • 68.5. The Initialization Fork
      • 68.6. Database Page Layout
    • 69. System Catalog Declarations and Initial Contents
    • 70. 查詢計畫如何使用統計資訊
      • 70.1. Row Estimation Examples
      • 70.2. 多元統計資訊範例
      • 70.3. Planner Statistics and Security
    • 71. Backup Manifest Format
  • VIII. 附錄
    • A. PostgreSQL 錯誤代碼
    • B. 日期時間格式支援
      • B.1. 日期時間解譯流程
      • B.2. 日期時間慣用字
      • B.3. 日期時間設定檔
      • B.4. 日期時間的沿革
    • C. SQL 關鍵字
    • D. SQL 相容性
      • D.1. Supported Features
      • D.2. Unsupported Features
      • D.3. XML Limits and Conformance to SQL/XML
    • E. 版本資訊
      • E.1. Release 14
    • F. 延伸支援模組
      • F.1. adminpack
      • F.2. amcheck
      • F.3. auth_delay
      • F.4. auto_explain
      • F.5. bloom
      • F.6. btree_gin
      • F.10. dblink
        • dblink_connect
        • dblink_connect_u
        • dblink_disconnect
        • dblink
        • dblink_exec
        • dblink_open
        • dblink_fetch
        • dblink_close
        • dblink_get_connections
        • dblink_error_message
        • dblink_send_query
        • dblink_is_busy
        • dblink_get_notify
        • dblink_get_result
        • dblink_cancel_query
        • dblink_get_pkey
        • dblink_build_sql_insert
        • dblink_build_sql_delete
        • dblink_build_sql_update
      • F.13. earthdistance
      • F.14. file_fdw
      • F.16. hstore
      • F.24. pg_buffercache
      • F.29. pg_stat_statements
      • F.30. pgstattuple
      • F.31. pg_trgm
      • F.32. pg_visibility
      • F.33. postgres_fdw
      • F.35. sepgsql
      • F.38. tablefunc
      • F.40. test_decoding
      • F.41. tsm_system_rows
      • F.42. tsm_system_time
      • F.44. uuid-ossp
    • G. Additional Supplied Programs
      • G.1. Client Applications
        • oid2name
        • vacuumlo
      • G.2. Server Applications
        • pg_standby
    • H. 外部專案
      • H.1. 用戶端介面
      • H.2. Administration Tools
      • H.3. Procedural Languages
      • H.4. Extensions
    • I. The Source Code Repository
      • I.1. Getting The Source via Git
    • J. 文件取得
    • K. PostgreSQL Limits
    • L. 縮寫字
    • M. Glossary
    • N. 色彩支援
      • N.1. When Color is Used
      • N.2. Configuring the Colors
  • 參考書目
Powered by GitBook
On this page
  • 語法
  • 說明
  • 參數
  • 注意
  • 範例
  • 相容性
  • 參閱

Was this helpful?

Edit on GitHub
Export as PDF
  1. VI. 參考資訊
  2. I. SQL 指令

CREATE INDEX

PreviousCREATE FUNCTIONNextCREATE LANGUAGE

Last updated 3 years ago

Was this helpful?

CREATE INDEX — 定義一個新的索引

語法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

說明

CREATE INDEX 在指定關連的指定欄位上建構索引,該索引可以是資料表或具體化檢視表。索引主要用於增強資料庫效能(儘管不恰當的使用會導致效能降低)。

索引的主要欄位指定欄位名稱,或者作為括號中的表示式指定。如果索引方法支援多欄位索引,則可以指定多個欄位。

索引欄位可以是根據資料表的一個欄位或多個欄位的計算表示式。此功能可用於基於對基本資料的某些轉換來快速存取資料。例如,在 upper(col) 上計算的索引將允許子句 WHERE upper(col) = 'JIM' 使用索引。

PostgreSQL 提供索引方法 B-tree,hash,GiST,SP-GiST,GIN 和 BRIN。使用者也可以定義自己的索引方法,但這相當複雜。

WHERE 子句存在時,將建立部分索引。部分索引是一個索引,它只包含資料表的一部分項目,通常是比索引的其餘部分更有用的索引部分。例如,如果您的資料表包含已開票和未開單的訂單,其中未開單的訂單佔據總資料表的一小部分,但這是一個經常使用的部分,您可以透過僅在該部分上建立索引來提高效能。另一個可能的應用是使用帶有 UNIQUE 的 WHERE 來強制資料表子集的唯一性。有關更多討論,請參閱。

WHERE 子句中使用的表示式只能引用基礎資料表的欄位,但它可以使用所有欄位,而不僅僅是被索引的欄位。目前,WHERE 中也禁止使用子查詢和彙總資料表示式。相同的限制適用於作為表示式的索引欄位。

索引定義中使用的所有函數和運算符必須是「immutable」,也就是說,它們的結果必須僅依賴於它們的參數,而不是任何外部影響(例如另一個資料表的內容或目前時間)。此限制可確保明確定義索引的行為。要在索引表示式或 WHERE 子句中使用使用者定義的函數,請記住在建立函數時將該函數標記為 immutable。

參數

UNIQUE

在建立索引時(如果資料已存在)並且每次插入資料時,系統都會檢查資料表中的重複值。嘗試插入或更新如果導致重複項目的資料將產生錯誤。

CONCURRENTLY

IF NOT EXISTS

如果已存在具有相同名稱的關連,請不要拋出錯誤,在這種情況下發出 NOTICE。請注意,無法保證現有索引與已建立的索引類似。指定 IF NOT EXISTS 時需要索引名稱。

INCLUDE

The optional INCLUDE clause specifies a list of columns which will be included in the index as non-key columns. A non-key column cannot be used in an index scan search qualification, and it is disregarded for purposes of any uniqueness or exclusion constraint enforced by the index. However, an index-only scan can return the contents of non-key columns without having to visit the index's table, since they are available directly from the index entry. Thus, addition of non-key columns allows index-only scans to be used for queries that otherwise could not use them.

It's wise to be conservative about adding non-key columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches. Furthermore, B-tree deduplication is never used with indexes that have a non-key column.

Columns listed in the INCLUDE clause don't need appropriate operator classes; the clause can include columns whose data types don't have operator classes defined for a given access method.

Expressions are not supported as included columns since they cannot be used in index-only scans.

Currently, the B-tree and the GiST index access methods support this feature. In B-tree and the GiST indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation.

name

要建立的索引名稱。這裡不能包含綱要名稱;索引始終在與其父資料表相同的綱要中創建。如果省略該名稱,PostgreSQL會根據父資料表的名稱和索引的欄位名稱選擇合適的名稱。

ONLY

Indicates not to recurse creating indexes on partitions, if the table is partitioned. The default is to recurse.

table_name

要編制索引的資料表名稱(可以加上綱要名稱)。

method

要使用的索引方法的名稱。選項是 btree,hash,gist,spgist,gin 和 brin。預設方法是 btree。

column_name

資料表欄位的名稱。

expression

基於資料表的一個欄位或多個欄位的表示式。表示式通常必須與周圍的括號一起填寫,如語法中所示。但是,如果表示式具有函數呼叫的形式,則可以省略括號。

collation

用於索引的排序規則的名稱。預設情況下,索引使用為要索引的欄位宣告排序規則或要索引的表示式結果排序規則。具有非預設排序規則的索引對於涉及使用非預設排序規則的表示式查詢非常有用。

opclass

運算子類的名稱。請參閱下文了解詳情。

opclass_parameter

The name of an operator class parameter. See below for details.

ASC

指定遞增排序順序(預設值)。

DESC

指定遞減排序。

NULLS FIRST

指定 nulls 排在非 null 之前。這是指定 DESC 時的預設值。

NULLS LAST

指定 nulls 排在非 null 之後。這是未指定 DESC 時的預設值。

storage_parameter

tablespace_name

predicate

部分索引的限制條件表示式。

索引儲存參數

選擇性的 WITH 子句指定索引的儲存參數。每個索引方法都有自己的一組允許的儲存參數。B-tree,hash,GiST 和 SP-GiST 索引方法都接受此參數:

fillfactor

索引的 fillfactor 一個百分比,用於確定索引方法嘗試填充索引頁面的程度。對於B-tree,在初始索引建構期間以及在右側擴展索引時(在插入新的最大索引值時),葉子頁面將填充到此百分比。 如果頁面隨後變得完整,它們將被拆分,導致索引效率逐漸下降。B-tree 使用預設的 fillfactor 為90,但可以選擇 10 到 100 之間的任何整數值。如果資料表是靜態的,那麼 fillfactor 100 能最小化索引的實體大小,但是對於大量更新的資料表,較小的 fillfactor 能最小化頁面拆分的需要。其他索引方法以不同但大致類似的方式使用 fillfactor;預設的 fillfactor 會因方法而異。

B-tree 索引也接受以下參數:

deduplicate_items (boolean)

透過 ALTER INDEX 關閉 deduplicate_items 可以防止後續的插入觸發重複資料刪除,但是它本身並不會使現有的資料使用標準資料表示形式。

vacuum_cleanup_index_scale_factor (floating point)

GiST 索引另外接受此參數:

buffering

GIN 索引接受不同的參數:

fastupdate

透過 ALTER INDEX 關閉 fastupdate 可防止將來的插入進入擱置的索引項目列表,但本身不會更新以前的項目。您可能希望 VACUUM 資料表或之後呼叫 gin_clean_pending_list 函數以確保清空擱置列表。

gin_pending_list_limit

BRIN 索引接受不同的參數:

pages_per_range

autosummarize

定義每當在下一個頁面上檢測到插入時是否為前一頁面範圍進行摘要計算。

同步建立索引

建立索引可能會干擾資料庫的日常操作。通常,PostgreSQL 會鎖定要對寫入進行索引的資料表,並通過對資料的單次掃描來執行整個索引建構。其他事務仍然可以讀取資料表,但如果它們嘗試插入,更新或刪除資料表中的資料列,它們將被阻擋,直到索引建構完成。如果系統是線上正式資料庫,這可能會產生嚴重影響。非常大的資料表可能需要很長時間才能被編入索引,即使對於較小的資料表,索引建構也可能會鎖定寫入程序,這些時間對於線上正式系統來說是不可接受的。

PostgreSQL 支援建構索引而不會鎖定寫入。透過指定 CREATE INDEX 的 CONCURRENTLY 選項來呼叫此方法。使用此選項時,PostgreSQL 必須對資料執行兩次掃描,此外,它必須等待可能修改或使用索引的所有事務。因此,這種方法比標準索引建構需要更多的工作,也需要更長的時間來完成。但是,由於它允許在建構索引時繼續正常操作,因此此方法對於在正式環境中增加新的索引很有用。當然,索引建立帶來的額外 CPU 和 I/O 負載可能會減慢其他操作。

如果在掃描資料表時出現問題,例如鎖死或唯一索引中的唯一性違規,則 CREATE INDEX 指令將會失敗但留下「無效」索引。出於查詢目的,該索引將被忽略,因為它可能不完整;但它仍然會消耗更新成本。psql \d 指令將回報此類索引為 INVALID:

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

在這種情況下,建議的恢復方法是刪除索引並再次嘗試同時執行 CREATE INDEX。(另一種可能性是使用 REINDEX 重建索引。但是,由於 REINDEX 不支持同步建構,因此該選項看起來不太有吸引力。)

同時建構唯一索引時的另一個警告是,當第二個資料表掃描開始時,已經對其他事務強制加上唯一性限制條件。這意味著在索引可供使用之前,甚至在索引建構最終失敗的情況下,可以在其他查詢中回報違反限制條件。此外,如果在第二次掃描中確實發生了故障,則「無效」索引將繼續強制執行其唯一性約束。

表示式索引和部分索引的同步建構也是支援的。在評估這些表示式時發生的錯誤可能導致類似於上面針對唯一性違規所描述的行為。

一般索引建立允許同一資料表上的其他一般索引建立同時執行,但一次只能在一個資料表上進行一個同步索引構立。在這兩種情況下,同時不允許在資料表上進行其他類型的結構變更。另一個區別是可以在事務塊中執行一般的 CREATE INDEX 指令,但 CREATE INDEX CONCURRENTLY 不能。

注意

目前,只有B-tree,GiST,GIN 和 BRIN 索引方法支持多欄位索引。預設情況下最多可以指定 32 個欄位。(編譯 PostgreSQL 時可以變更此限制。)只有 B-tree 目前支援唯一索引。

When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.

對於支援有序掃描的索引方法(目前只有 B-tree),可以指定選擇性子句 ASC,DESC,NULLS FIRST 和 NULLS LAST 來修改索引的排序順序。由於可以向前或向後掃描有序索引,因此建立單欄位 DESC 索引並不常用 - 排序順序已經可用於一般性索引。這些選項的值是可以建立多欄位索引,該索引搭配混合排序查詢所請求的排序順序,例如 SELECT ... ORDER BY x ASC, y DESC。如果您需要在依賴於索引以避免排序步驟的查詢中支援「nulls sort low」行為而不是預設的「nulls sort high」,那麼 NULLS 選項很有用。

PostgreSQL can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as parallel index build. For index methods that support building indexes in parallel (currently, only B-tree), maintenance_work_mem specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. Generally, a cost model automatically determines how many worker processes should be requested, if any.

Tip

You might want to reset parallel_workers after setting it as part of tuning an index build. This avoids inadvertent changes to query plans, since parallel_workers affects all parallel table scans.

While CREATE INDEX with the CONCURRENTLY option supports parallel builds without special restrictions, only the first table scan is actually performed in parallel.

PostgreSQL 的早期版本也有一個 R-tree 索引方法。此方法已被移除,因為它沒有 GiST 方法的顯著優勢。如果指定了 USING rtree,CREATE INDEX 會將其解釋為USING gist,以簡化舊資料庫到 GiST 的轉換。

範例

To create a unique B-tree index on the column title in the table films:

CREATE UNIQUE INDEX title_idx ON films (title);

To create a unique B-tree index on the column title with included columns director and rating in the table films:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

To create a B-Tree index with deduplication disabled:

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

To create an index on the expression lower(title), allowing efficient case-insensitive searches:

CREATE INDEX ON films ((lower(title)));

(In this example we have chosen to omit the index name, so the system will choose a name, typically films_lower_idx.)

To create an index with non-default collation:

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

To create an index with non-default sort ordering of nulls:

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

To create an index with non-default fill factor:

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

To create a GIN index with fast updates disabled:

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

To create an index on the column code in the table films and have the index reside in the tablespace indexspace:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

To create a GiST index on a point attribute so that we can efficiently use box operators on the result of the conversion function:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

To create an index without locking out writes to the table:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

相容性

CREATE INDEX 是 PostgreSQL 延伸語法。SQL 標準中沒有索引的規定。

參閱

使用此選項時,PostgreSQL 將在建立索引時,不會採取任何阻止資料表上同時的插入,更新或刪除的鎖定;而標準索引建立會鎖定資料表上的寫入(但不是讀取),直到完成為止。使用此選項時需要注意幾點 - 請參閱。

特定於索引方法的儲存參數的名稱。有關詳情,請參見。

用於建立索引的資料表空間。如果未指定,則查詢 ,或臨時資料表 。

控制中所描述的 B-tree 重複資料刪除技術的使用。設定為 ON 或 OFF 以啟用或停用該機制。(如所述,允許使用 ON 和 OFF 的其他寫法。)預設值為 ON。

Per-index value for .

確定是否使用中描述的緩衝建構技術來建構索引。使用 OFF 時,它被停用,ON 時啟用。當使用 AUTO 時,它最初被停用,但一旦索引大小達到 ,就會立即打開。預設值為 AUTO。

此設定控制中描述的快速更新技術的運用。它是一個布林參數:ON 啟用快速更新,OFF 停用它。 (如所述,允許使用 ON 和 OFF 的替代拼寫。)預設為 ON。

自定義 參數。此值以 KB 為單位。

定義構成 BRIN 索引每個項目的一個區塊範圍的資料表區塊數(有關更多詳細訊息,請參閱)。預設值為 128。

在同步索引建構時,索引實際上在一個交易事務中輸入到系統目錄,然後在另外兩個事務中産生兩個資料表掃描。在每次掃描資料表之前,索引建構必須等待已修改資料表的現有事務結束。在第二次掃描之後,索引建構必須等待具有快照(參閱)的任何事務在第二次掃描之前結束。最後,索引可以標記為可以使用,然後 CREATE INDEX 指令完成。但是,即使這樣,索引也可能無法立即用於查詢:在最壞的情況下,只要在索引建構開始之前存在事務,都不能使用它。

有關何時可以使用索引,何時不使用索引以及哪些特定情況可以使用索引的訊息,請參閱。

可以為索引的每個欄位指定運算子類。運算子類識別該欄位的索引要使用的運算子。例如,4 bytes 整數的 B-tree 索引將使用 int4_ops 類;此運算子類包括 4 bytes 整數的比較函數。實際上,欄位資料型別的預設運算子類通常就足夠了。擁有運算子類的要點是,對於某些資料型別,可能存在多個有意義的排序。例如,我們可能希望按絕對值或複數資料型別的實部進行排序。我們可以透過為資料型別定義兩個運算子類,然後在建立索引時選擇適當的類。有關運算子類的更多訊息,請參閱和。

對於大多數索引方法,建立索引的速度取決於 的設定。較大的值將減少索引建立所需的時間,只要您不要使其大於真正可用的記憶體大小,否則將驅使主機使用 SWAP。

Parallel index builds may benefit from increasing maintenance_work_mem where an equivalent serial index build will see little or no benefit. Note that maintenance_work_mem may influence the number of worker processes requested, since parallel workers must have at least a 32MB share of the total maintenance_work_mem budget. There must also be a remaining 32MB share for the leader process. Increasing may allow more workers to be used, which will reduce the time needed for index creation, so long as the index build is not already I/O bound. Of course, there should also be sufficient CPU capacity that would otherwise lie idle.

Setting a value for parallel_workers via directly controls how many parallel worker processes will be requested by a CREATE INDEX against the table. This bypasses the cost model completely, and prevents maintenance_work_mem from affecting how many parallel workers are requested. Setting parallel_workers to 0 via ALTER TABLE will disable parallel index builds on the table in all cases.

使用 移除索引。

, ,

第 11.8 節
第 65.1 節
第 13 章
第 11 章
第 11.10 節
第 37.16 節
max_parallel_maintenance_workers
ALTER TABLE
DROP INDEX
ALTER INDEX
DROP INDEX
REINDEX
同步建立索引
索引儲存參數
第 19.1 節
第 19.1 節
default_tablespace
temp_tablespaces
vacuum_cleanup_index_scale_factor
gin_pending_list_limit
maintenance_work_mem
第 64.4.1 節
effective_cache_size
第 63.4.2 節
第 62.4.1 節