PostgreSQL 9.2.2

PostgreSQL 是一款功能強大、開放原始碼的物件關聯式資料庫系統。它歷經超過 15 年的積極開發，其成熟的架構為可靠性、資料完整性與正確性贏得了良好聲譽。

它可在所有主流作業系統上執行，包括 Linux、UNIX（AIX、BSD、HP-UX、SGI IRIX、macOS、Solaris、Tru64）以及 Windows。PostgreSQL 是一款強大的物件關聯式資料庫管理系統！下載適用於 PC 的 PostgreSQL 離線安裝程式 64 位元版本！

它完全符合 ACID 規範，並完整支援外部鍵、聯結、檢視、觸發器以及預存程序（支援多種語言）。它包含大多數 SQL:2008 資料型別，包括 INTEGER、NUMERIC、BOOLEAN、CHAR、VARCHAR、DATE、INTERVAL 和 TIMESTAMP。

它也支援儲存二進位大型物件，包括圖片、音訊或影片。它具備 C/C++、Java、.Net、Perl、Python、Ruby、Tcl、ODBC 等原生程式設計介面，並提供卓越的文件（資料表大小最高可達 32 TB）。

PostgreSQL 2026 具備許多功能，旨在協助開發人員建置應用程式、協助管理員保護資料完整性並建置容錯環境，同時協助您管理任何規模的資料集。除了免費且開放原始碼之外，這款工具還具有高度擴充性。

例如，您可以定義自己的資料型別、建立自訂函式，甚至使用不同的程式語言撰寫程式碼，而無需重新編譯資料庫！

此應用程式力求在符合 SQL 標準的同時，不與傳統功能牴觸，也不會導致不良的架構決策。

SQL 標準要求的許多功能都已支援，不過有時語法或功能會略有差異。未來可望進一步提升標準符合度。

功能與重點

資料型別

• 基本型別：整數、數值、字串、布林值
• 結構化：日期/時間、陣列、範圍、UUID
• 文件型別：JSON/JSONB、XML、鍵值對（Hstore）
• 幾何型別：點、線、圓、多邊形
• 自訂：複合型別、自訂型別

資料完整性

• UNIQUE、NOT NULL
• 主鍵
• 外部鍵
• 排除約束
• 明確鎖定、建議鎖定

並行性與效能

• 索引：B-tree、多欄位、運算式、部分索引
• 進階索引：GiST、SP-GiST、KNN GiST、GIN、BRIN、涵蓋索引、布隆過濾器
• 精密的查詢規劃器/最佳化器、僅索引掃描、多欄位統計
• 交易、巢狀交易（透過儲存點）
• 多版本並行控制（MVCC）
• 讀取查詢與建立 B-tree 索引的平行化
• 資料表分割
• SQL 標準定義的所有交易隔離層級，包括可序列化
• 運算式的即時（JIT）編譯

可靠性與災難復原

• 預寫式記錄（WAL）
• 複寫：非同步、同步、邏輯
• 時間點復原（PITR）、主動備援
• 資料表空間

安全性

• 驗證：GSSAPI、SSPI、LDAP、SCRAM-SHA-256、憑證等
• 健全的存取控制系統
• 欄位與列層級安全性

擴充性

• 預存函式與程序
• 程序語言：PL/pgSQL、Perl、Python（以及更多）
• 外部資料包裝器：透過標準 SQL 介面連線至其他資料庫或串流
• 許多提供額外功能的擴充套件，包括 PostGIS

國際化與全文檢索

• 支援國際字元集，例如透過 ICU 定序
• 全文檢索

使用方式

• 從官方網站或 FileHorse.com 下載並安裝此程式
• 開啟 pgAdmin 或使用命令列進行資料庫管理
• 使用 pgAdmin 介面建立新資料庫
• 使用 SQL 查詢來建立、讀取、更新和刪除資料
• 設定使用者角色與權限以確保安全性
• 使用 pgAdmin 或命令列備份與還原資料庫
• 使用索引與查詢調校來最佳化效能
• 使用 PostgreSQL 相容驅動程式連線應用程式
• 使用內建記錄工具監控資料庫活動
• 保持 PostgreSQL 更新以確保安全性與效能

系統需求

作業系統：Windows 11 或 Windows 10（64 位元）

處理器：Intel 或 AMD 64 位元處理器

記憶體：最低 2GB，建議 4GB 或以上

儲存空間：安裝至少需要 100MB，資料儲存另需更多空間

其他需求：需要 Microsoft Visual C++ 可轉散發套件

優點

• 開放原始碼且免費使用
• 進階安全性功能
• 高擴充性與效能
• 支援複雜查詢與索引
• 強大的社群與文件

缺點

• 需要手動效能調校
• 某些情況下記憶體使用量較高
• 內建 GUI 管理工具有限
• 升級可能需要手動調整

另有提供：下載 PostgreSQL for Mac

下載 PostgreSQL 最新版本 為什麼此應用程式發佈在 FileHorse 上？（更多資訊）

# Changes
* Fix multiple bugs associated with CREATE/DROP INDEX CONCURRENTLY
- An error introduced while adding DROP INDEX CONCURRENTLY allowed incorrect indexing decisions to be made during the initial phase of CREATE INDEX CONCURRENTLY; so that indexes built by that command could be corrupt. It is recommended that indexes built in 9.2.X with CREATE INDEX CONCURRENTLY be rebuilt after applying this update.
- In addition, fix CREATE/DROP INDEX CONCURRENTLY to use in-place updates when changing the state of an index's pg_index row. This prevents race conditions that could cause concurrent sessions to miss updating the target index, thus again resulting in corrupt concurrently-created indexes.
- Also, fix various other operations to ensure that they ignore invalid indexes resulting from a failed CREATE INDEX CONCURRENTLY command. The most important of these is VACUUM, because an auto-vacuum could easily be launched on the table before corrective action can be taken to fix or remove the invalid index.
- Also fix DROP INDEX CONCURRENTLY to not disable insertions into the target index until all queries using it are done.
- Also fix misbehavior if DROP INDEX CONCURRENTLY is canceled: the previous coding could leave an un-droppable index behind.
* Correct predicate locking for DROP INDEX CONCURRENTLY
- Previously, SSI predicate locks were processed at the wrong time, possibly leading to incorrect behavior of serializable transactions executing in parallel with the DROP.
* Fix buffer locking during WAL replay
- The WAL replay code was insufficiently careful about locking buffers when replaying WAL records that affect more than one page. This could result in hot standby queries transiently seeing inconsistent states, resulting in wrong answers or unexpected failures.
* Fix an error in WAL generation logic for GIN indexes
- This could result in index corruption, if a torn-page failure occurred.
* Fix an error in WAL replay logic for SP-GiST indexes
- This could result in index corruption after a crash, or on a standby server.
* Fix incorrect detection of end-of-base-backup location during WAL recovery
- This mistake allowed hot standby mode to start up before the database reaches a consistent state.
* Properly remove startup process's virtual XID lock when promoting a hot standby server to normal running
- This oversight could prevent subsequent execution of certain operations such as CREATE INDEX CONCURRENTLY.
* Avoid bogus "out-of-sequence timeline ID" errors in standby mode
* Prevent the postmaster from launching new child processes after it's received a shutdown signal
- This mistake could result in shutdown taking longer than it should, or even never completing at all without additional user action.
* Fix the syslogger process to not fail when log_rotation_age exceeds 2^31 milliseconds (about 25 days)
* Fix WaitLatch() to return promptly when the requested timeout expires
- With the previous coding, a steady stream of non-wait-terminating interrupts could delay return from WaitLatch() indefinitely. This has been shown to be a problem for the autovacuum launcher process, and might cause trouble elsewhere as well.
* Avoid corruption of internal hash tables when out of memory
* Prevent file descriptors for dropped tables from being held open past transaction end
- This should reduce problems with long-since-dropped tables continuing to occupy disk space.
* Prevent database-wide crash and restart when a new child process is unable to create a pipe for its latch
- Although the new process must fail, there is no good reason to force a database-wide restart, so avoid that. This improves robustness when the kernel is nearly out of file descriptors.
* Avoid planner crash with joins to unflattened subqueries
* Fix planning of non-strict equivalence clauses above outer joins
- The planner could derive incorrect constraints from a clause equating a non-strict construct to something else, for example WHERE COALESCE(foo, 0) = 0 when foo is coming from the nullable side of an outer join. 9.2 showed this type of error in more cases than previous releases, but the basic bug has been there for a long time.
* Fix SELECT DISTINCT with index-optimized MIN/MAX on an inheritance tree
- The planner would fail with "failed to re-find MinMaxAggInfo record" given this combination of factors.
* Make sure the planner sees implicit and explicit casts as equivalent for all purposes, except in the minority of cases where there's actually a semantic difference
* Include join clauses when considering whether partial indexes can be used for a query
- A strict join clause can be sufficient to establish an x IS NOT NULL predicate, for example. This fixes a planner regression in 9.2, since previous versions could make comparable deductions.
* Limit growth of planning time when there are many indexable join clauses for the same index
* Improve planner's ability to prove exclusion constraints from equivalence classes
* Fix partial-row matching in hashed subplans to handle cross-type cases correctly
- This affects multicolumn NOT IN subplans, such as WHERE (a, b) NOT IN (SELECT x, y FROM ...) when for instance b and y are int4 and int8 respectively. This mistake led to wrong answers or crashes depending on the specific datatypes involved.
* Fix btree mark/restore functions to handle array keys
- This oversight could result in wrong answers from merge joins whose inner side is an index scan using an indexed_column = ANY(array) condition.
* Revert patch for taking fewer snapshots
- The 9.2 change to reduce the number of snapshots taken during query execution led to some anomalous behaviors not seen in previous releases, because execution would proceed with a snapshot acquired before locking the tables used by the query. Thus, for example, a query would not be guaranteed to see updates committed by a preceding transaction even if that transaction had exclusive lock. We'll probably revisit this in future releases, but meanwhile put it back the way it was before 9.2.
* Acquire buffer lock when re-fetching the old tuple for an AFTER ROW UPDATE/DELETE trigger
- In very unusual circumstances, this oversight could result in passing incorrect data to a trigger WHEN condition, or to the precheck logic for a foreign-key enforcement trigger. That could result in a crash, or in an incorrect decision about whether to fire the trigger.
* Fix ALTER COLUMN TYPE to handle inherited check constraints properly
- This worked correctly in pre-8.4 releases, and now works correctly in 8.4 and later.
* Fix ALTER EXTENSION SET SCHEMA's failure to move some subsidiary objects into the new schema
* Handle CREATE TABLE AS EXECUTE correctly in extended query protocol
* Don't modify the input parse tree in DROP RULE IF NOT EXISTS and DROP TRIGGER IF NOT EXISTS
- This mistake would cause errors if a cached statement of one of these types was re-executed.
* Fix REASSIGN OWNED to handle grants on tablespaces
* Ignore incorrect pg_attribute entries for system columns for views
- Views do not have any system columns. However, we forgot to remove such entries when converting a table to a view. That's fixed properly for 9.3 and later, but in previous branches we need to defend against existing mis-converted views.
* Fix rule printing to dump INSERT INTO table DEFAULT VALUES correctly
* Guard against stack overflow when there are too many UNION/INTERSECT/EXCEPT clauses in a query
* Prevent platform-dependent failures when dividing the minimum possible integer value by -1
* Fix possible access past end of string in date parsing
* Fix failure to advance XID epoch if XID wraparound happens during a checkpoint and wal_level is hot_standby
- While this mistake had no particular impact on PostgreSQL itself, it was bad for applications that rely on txid_current() and related functions: the TXID value would appear to go backwards.
* Fix pg_terminate_backend() and pg_cancel_backend() to not throw error for a non-existent target process
- This case already worked as intended when called by a superuser, but not so much when called by ordinary users.
* Fix display of pg_stat_replication.sync_state at a page boundary
* Produce an understandable error message if the length of the path name for a Unix-domain socket exceeds the platform-specific limit
- Formerly, this would result in something quite unhelpful, such as "Non-recoverable failure in name resolution".
* Fix memory leaks when sending composite column values to the client
* Save some cycles by not searching for subtransaction locks at commit
- In a transaction holding many exclusive locks, this useless activity could be quite costly.
* Make pg_ctl more robust about reading the postmaster.pid file
- This fixes race conditions and possible file descriptor leakage.
* Fix possible crash in psql if incorrectly-encoded data is presented and the client_encoding setting is a client-only encoding, such as SJIS
* Make pg_dump dump SEQUENCE SET items in the data not pre-data section of the archive
- This fixes an undesirable inconsistency between the meanings of --data-only and --section=data, and also fixes dumping of sequences that are marked as extension configuration tables.
* Fix pg_dump's handling of DROP DATABASE commands in --clean mode
- Beginning in 9.2.0, pg_dump --clean would issue a DROP DATABASE command, which was either useless or dangerous depending on the usage scenario. It no longer does that. This change also fixes the combination of --clean and --create to work sensibly, i.e., emit DROP DATABASE then CREATE DATABASE before reconnecting to the target database.
* Fix pg_dump for views with circular dependencies and no relation options
- The previous fix to dump relation options when a view is involved in a circular dependency didn't work right for the case that the view has no options; it emitted ALTER VIEW foo SET () which is invalid syntax.
* Fix bugs in the restore.sql script emitted by pg_dump in tar output format
- The script would fail outright on tables whose names include upper-case characters. Also, make the script capable of restoring data in --inserts mode as well as the regular COPY mode.
* Fix pg_restore to accept POSIX-conformant tar files
- The original coding of pg_dump's tar output mode produced files that are not fully conformant with the POSIX standard. This has been corrected for version 9.3. This patch updates previous branches so that they will accept both the incorrect and the corrected formats, in hopes of avoiding compatibility problems when 9.3 comes out.
* Fix tar files emitted by pg_basebackup to be POSIX conformant
* Fix pg_resetxlog to locate postmaster.pid correctly when given a relative path to the data directory
- This mistake could lead to pg_resetxlog not noticing that there is an active postmaster using the data directory.
* Fix libpq's lo_import() and lo_export() functions to report file I/O errors properly
* Fix ecpg's processing of nested structure pointer variables
* Fix ecpg's ecpg_get_data function to handle arrays properly
* Prevent pg_upgrade from trying to process TOAST tables for system catalogs
- This fixes an error seen when the information_schema has been dropped and recreated. Other failures were also possible.
* Improve pg_upgrade performance by setting synchronous_commit to off in the new cluster
* Make contrib/pageinspect's btree page inspection functions take buffer locks while examining pages
* Work around unportable behavior of malloc(0) and realloc(NULL, 0)
- On platforms where these calls return NULL, some code mistakenly thought that meant out-of-memory. This is known to have broken pg_dump for databases containing no user-defined aggregates. There might be other cases as well.
* Ensure that make install for an extension creates the extension installation directory
- Previously, this step was missed if MODULEDIR was set in the extension's Makefile.
* Fix pgxs support for building loadable modules on AIX
- Building modules outside the original source tree didn't work on AIX.
* Update time zone data files to tzdata release 2012j for DST law changes in Cuba, Israel, Jordan, Libya, Palestine, Western Samoa, and portions of Brazil.