PostgreSQL內建支援智慧式日期時間輸入格式。日期及時間以字串格式輸入，以預先定義的方式，分隔為多個欄位。每一個欄位可能會被解譯為數字、忽視或拒絕。處理程式也內建常見的描述字，如月份、星期及時區名稱。

本文件說明日期及時間解譯的方法和步驟，也包含了常見描述字的列表。

• https://www.postgresql.org/docs/10/static/datetime-appendix.html

所有由PostgreSQL伺服器回傳的訊息，都會搭配一個 5 個字元的錯誤代碼，這些代碼均以 SQL 標準的 SQLSTATE 代碼所定義。應用程式應該以錯誤代碼作為程式行為，而非處理文字的錯誤訊息。這些錯誤代碼會隨 PostgreSQL 版本發佈而微幅更新，但不會因為不同語言的文字訊息影響其定義。特別注意，部份錯誤代碼是以 SQL 標準的定義所制定，而有些錯誤代碼是額外制定的，以對應其他 SQL 標準未定義的使用情況。

標準來看，錯誤代碼的前 2 個字元代表著這個錯誤的類別，而後面 3 個字元則指出在此類別中更詳細的發生情況。所以，應用程式即使未完整支援特定的錯誤代碼，仍然可以依類別代碼瞭解大概發生了什麼事。

下方的 Table A.1 列出了所有在 PostgreSQL 10 中所定義的錯誤代碼。（部份錯誤代碼可能實際上沒有使用，但仍然依 SQL 標準所制定）每一個錯誤的類別都有一個＂標準＂的錯誤情況，其最後 3 個字元為 000，只用於在該類別未特別詳細定義的錯誤情況。

在＂Condition Name＂欄位中的內容將能在 PL/pgSQL 中被使用。Condition Name 不論大小寫字母均可合法使用。（特別注意，PL/pgSQL 並不支援 00、01 及 02 的錯誤類別。）

有一些錯誤訊息，其回傳內容是發生錯誤的資料庫物件名稱（表格 Table、表格欄位 Table column、資料型別 data type、限制條件 constraint）。舉例來說，如果產生了 unique_violation 錯誤，則會回傳某個限制條件名稱。這些訊息將會分別額外的欄位回傳，所以應用程式不需要特別解譯描述性的訊息內容。在 PostgreSQL 9.3之前，這種回傳方式僅限於 SQLSTATE 類別 23（違反限制條件的一致性 integrity constraint violation），日後將儘可能延伸支援到所有類別。

Table A.1. PostgreSQL Error Codes

• https://www.postgresql.org/docs/10/static/errcodes-appendix.html

The SQL standard states that“Within the definition of a‘datetime literal’, the‘datetime values’are constrained by the natural rules for dates and times according to the Gregorian calendar”.PostgreSQLfollows the SQL standard's lead by counting dates exclusively in the Gregorian calendar, even for years before that calendar was in use. This rule is known as theproleptic Gregorian calendar.

The Julian calendar was introduced by Julius Caesar in 45 BC. It was in common use in the Western world until the year 1582, when countries started changing to the Gregorian calendar. In the Julian calendar, the tropical year is approximated as 365 1/4 days = 365.25 days. This gives an error of about 1 day in 128 years.

The accumulating calendar error prompted Pope Gregory XIII to reform the calendar in accordance with instructions from the Council of Trent. In the Gregorian calendar, the tropical year is approximated as 365 + 97 / 400 days = 365.2425 days. Thus it takes approximately 3300 years for the tropical year to shift one day with respect to the Gregorian calendar.

The approximation 365+97/400 is achieved by having 97 leap years every 400 years, using the following rules:

So, 1700, 1800, 1900, 2100, and 2200 are not leap years. But 1600, 2000, and 2400 are leap years. By contrast, in the older Julian calendar all years divisible by 4 are leap years.

The papal bull of February 1582 decreed that 10 days should be dropped from October 1582 so that 15 October should follow immediately after 4 October. This was observed in Italy, Poland, Portugal, and Spain. Other Catholic countries followed shortly after, but Protestant countries were reluctant to change, and the Greek Orthodox countries didn't change until the start of the 20th century. The reform was observed by Great Britain and its dominions (including what is now the USA) in 1752. Thus 2 September 1752 was followed by 14 September 1752. This is why Unix systems have thecalprogram produce the following:

$ cal 9 1752

   September 1752
 S  M Tu  W Th  F  S
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

But, of course, this calendar is only valid for Great Britain and dominions, not other places. Since it would be difficult and confusing to try to track the actual calendars that were in use in various places at various times,PostgreSQLdoes not try, but rather follows the Gregorian calendar rules for all dates, even though this method is not historically accurate.

Different calendars have been developed in various parts of the world, many predating the Gregorian system. For example, the beginnings of the Chinese calendar can be traced back to the 14th century BC. Legend has it that the Emperor Huangdi invented that calendar in 2637 BC. The People's Republic of China uses the Gregorian calendar for civil purposes. The Chinese calendar is used for determining festivals.

The_Julian Date_system is another type of calendar, unrelated to the Julian calendar though it is confusingly named similarly to that calendar. The Julian Date system was invented by the French scholar Joseph Justus Scaliger (1540-1609) and probably takes its name from Scaliger's father, the Italian scholar Julius Caesar Scaliger (1484-1558). In the Julian Date system, each day has a sequential number, starting from JD 0 (which is sometimes called_the_Julian Date). JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or 24 November 4714 BC in the Gregorian calendar. Julian Date counting is most often used by astronomers for labeling their nightly observations, and therefore a date runs from noon UTC to the next noon UTC, rather than from midnight to midnight: JD 0 designates the 24 hours from noon UTC on 24 November 4714 BC to noon UTC on 25 November 4714 BC.

AlthoughPostgreSQLsupports Julian Date notation for input and output of dates (and also uses Julian dates for some internal datetime calculations), it does not observe the nicety of having dates run from noon to noon.PostgreSQLtreats a Julian Date as running from midnight to midnight.

• https://www.postgresql.org/docs/10/static/datetime-units-history.html

lists all tokens that are key words in the SQL standard and in PostgreSQL 11devel. Background information can be found in . (For space reasons, only the latest two versions of the SQL standard, and SQL-92 for historical comparison, are included. The differences between those and the other intermediate standard versions are small.)

SQL distinguishes between reserved and non-reserved key words. According to the standard, reserved key words are the only real key words; they are never allowed as identifiers. Non-reserved key words only have a special meaning in particular contexts and can be used as identifiers in other contexts. Most non-reserved key words are actually the names of built-in tables and functions specified by SQL. The concept of non-reserved key words essentially only exists to declare that some predefined meaning is attached to a word in some contexts.

In the PostgreSQL parser life is a bit more complicated. There are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. (The latter is usually the case for functions specified by SQL.) Even reserved key words are not completely reserved in PostgreSQL, but can be used as column labels (for example, SELECT 55 AS CHECK, even though CHECK is a reserved key word).

In in the column for PostgreSQL we classify as “non-reserved” those key words that are explicitly known to the parser but are allowed as column or table names. Some key words that are otherwise non-reserved cannot be used as function or data type names and are marked accordingly. (Most of these words represent built-in functions or data types with special syntax. The function or type is still available but it cannot be redefined by the user.) Labeled “reserved” are those tokens that are not allowed as column or table names. Some reserved key words are allowable as names for functions or data types; this is also shown in the table. If not so marked, a reserved key word is only allowed as an “AS” column label name.

As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away.

It is important to understand before studying that the fact that a key word is not reserved in PostgreSQL does not mean that the feature related to the word is not implemented. Conversely, the presence of a key word does not indicate the existence of a feature.

Table C.1. SQL Key Words

由於時區縮寫並沒有很好地標準化，PostgreSQL 提供了一種自訂的伺服器接受的縮寫集方法。timezone_abbreviations 運行時參數決定有效的縮寫集。雖然此參數可由任何資料庫使用者變更，但其可能的值受資料庫管理員控制 - 實際上它們是儲存在安裝目錄的 .../share/timezonesets/ 中的組態檔案的名稱。透過增加或變更該目錄中的檔案，管理員可以為時區縮寫設定本地策略。

timezone_abbreviations 可以設定為在 .../share/timezonesets/ 中找到的任何檔案名稱，檔案的名稱需要完全是英文字母的。（禁止 timezone_abbreviations 中的非英文字母字元可以防止讀取目標目錄之外的檔案，以及讀取編輯器備份檔案和其他無關檔案。）

時區縮寫檔案可以包含空白行和以 # 開頭的註釋。非註釋行必須具有以下格式之一：

zone_abbreviation offset
zone_abbreviation offset D
zone_abbreviation time_zone_name
@INCLUDE file_name
@OVERRIDE

zone_abbreviation 只是定義的縮寫。offset 是一個整數，定義與 UTC 相等的偏移量，以秒為單位，正向格林威治為東，負向為西。例如，-18000 將在格林威治以西 5 小時或北美東海岸標準時間。D 的區域名稱表示本地夏令時間而不是標準時間。

或者，可以定義 time_zone_name，引用 IANA 時區資料庫中定義的區域名稱。查詢區域的定義以查看該區域中是否正在使用縮寫，如果是，則使用適當的含義 - 即，目前正在確定其值的時間戳記中使用的含義，或者如果當時不是目前使用的話，則使用之前的含義，如果僅在該時間之後使用，則使用最舊的含義。這種行為對於處理其含義歷史變化的縮寫至關重要。還允許根據區域名稱定義縮寫，其中不出現該縮寫；使用縮寫就等於寫出區域名稱。

小技巧 在定義與 UTC 的偏移量從未改變的縮寫時，偏好使用簡單的整數偏移量，因為這些縮寫比需要查閱時區定義的縮寫要簡單得多。

@INCLUDE 語法允許在 .../share/timezonesets/ 目錄中包含另一個檔案。包含可以嵌套到某個有限的深度。

@OVERRIDE 語法指示檔案中的後續項目可以覆蓋先前的項目（通常是從包含的檔案中獲取的項目）。如果沒有這個，相同時區縮寫的衝突定義將被視為錯誤。

在未修改的安裝環境中，檔案 Default 包含了世界上大多數地區的所有非衝突時區縮寫。為這些區域提供了澳大利亞和印度的附加檔案：這些檔案先有預設設定，然後根據需要增加或修改縮寫。

出於參考目的，標準安裝還包含 Africa.txt，America.txt 等文件，其中包含有關根據 IANA 時區資料庫已知正在使用的每個時區縮寫的訊息。可以根據需要將這些檔案中找到的區域名稱定義複製並貼到自行定義配置檔案中。請注意，由於名稱中包含了點，因此無法將這些檔案直接引用為 timezone_abbreviations 設定。

注意

如果在讀取時區縮寫集時發生錯誤，則不會應用新值並保留舊值。如果在啟動資料庫時發生錯誤，則啟動失敗。

配置檔案中定義的時區縮寫會覆寫 PostgreSQL 中內建的非時區定義。例如，澳大利亞配置檔案定義了 SAT（南澳大利亞標準時間）。當此檔案有效時，SAT 將不會被識別為星期六的縮寫。

如果您修改 .../share/timezonesets/ 中的檔案，則由您來進行備份 - 正常的資料庫轉存將不包含此目錄。

btree_gin provides sample GIN operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, "char", varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, name, bool, bpchar, and all enum types.

通常，這些運算子類不會優於等效的標準 B-tree 索引方法，並且它們缺少標準 B-tree 的一個主要功能：強制執行唯一性。但是，它們對於 GIN 測試很有用，並且可以作為開發其他 GIN 運算子類的基礎。同樣地，對於同時測試可索引 GIN 欄位和 B-tree 可索引列的查詢，建立使用這些運算子之一的多欄位 GIN 索引可能比建立兩個必須獨立的索引更有效，以 bitmap ANDing 的方式。

F.6.1. 使用範例

CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING GIN (a);
-- query
SELECT * FROM test WHERE a < 10;

F.6.2. 作者們

Teodor Sigaev (<teodor@stack.net>) and Oleg Bartunov (<oleg@sai.msu.su>). See http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gin for additional information.

This appendix and the next one contain information regarding the modules that can be found in the contrib directory of the PostgreSQL distribution. These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system, mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness.

This appendix covers extensions and other server plug-in modules found in contrib. Appendix G covers utility programs.

When building from the source distribution, these components are not built automatically, unless you build the "world" target (see Step 2). You can build and install all of them by running:

make
make install

in the contrib directory of a configured source tree; or to build and install just one selected module, do the same in that module's subdirectory. Many of the modules have regression tests, which can be executed by running:

make check

before installation or

make installcheck

once you have a PostgreSQL server running.

If you are using a pre-packaged version of PostgreSQL, these modules are typically made available as a separate subpackage, such as postgresql-contrib.

Many modules supply new user-defined functions, operators, or types. To make use of one of these modules, after you have installed the code you need to register the new SQL objects in the database system. In PostgreSQL 9.1 and later, this is done by executing a CREATE EXTENSION command. In a fresh database, you can simply do

CREATE EXTENSION module_name;

This command must be run by a database superuser. This registers the new SQL objects in the current database only, so you need to run this command in each database that you want the module's facilities to be available in. Alternatively, run it in database template1 so that the extension will be copied into subsequently-created databases by default.

Many modules allow you to install their objects in a schema of your choice. To do that, add SCHEMA schema_name to the CREATE EXTENSION command. By default, the objects will be placed in your current creation target schema, which in turn defaults to public.

If your database was brought forward by dump and reload from a pre-9.1 version of PostgreSQL, and you had been using the pre-9.1 version of the module in it, you should instead do

CREATE EXTENSION module_name FROM unpackaged;

This will update the pre-9.1 objects of the module into a proper extension object. Future updates to the module will be managed by ALTER EXTENSION. For more information about extension updates, see Section 37.17.

Note, however, that some of these modules are not “extensions” in this sense, but are loaded into the server in some other way, for instance by way of shared_preload_libraries. See the documentation of each module for details.

日期時間資料將會以下列流程解譯：（分隔符號均為半型文字）

1. 以分隔符號將其分解為多個段落，如字串、時區或數字。

   1. 如果是以冒號（:）分隔的數字格式，那麼這是一個時間的字串，其所包括的內容都是時間資訊的一部份。

   2. 如果是以連字號（-）、斜線（/）、或兩個以上的間隔號（.）所分隔的數字格式，那麼這是一個日期的字串，它可能包含文字型式的月份名稱。但如果日期分隔符號已經先出現了，那麼它將被解釋為時區資訊（例如：Asia/Taipei）。

   3. 如果整個字串都是數字所組成，那麼它可能是符合ISO 8601格式的日期（例如：19990113，表示西元1999年1月3日），或時間（例如：141516，表示14:15:16）。

   4. 如果它是以加號（+）或減號（-）開頭的話，那麼它是一個數字型態的時區資訊或是特別的區間。

2. 如果是一個字串，進行下列比對規則：

   1. 以binary-search表格，尋找時區的表示字。

   2. 如果沒有找到的話，則搜尋慣用字（如：today），星期（如：Thursday），月份（如：January），或介系詞（如：at, on）。

   3. 如果都沒有找到，就回傳錯誤訊息。

3. 如果是一個由數字組成的字串，則進行下列判斷：

   1. 如果是8個或6個數字，而先前也沒有讀到其他日期的資訊，那麼它會被解譯為一個數字型態的日期（如：19990118或990118），對應年月日格式為YYYYMMDD或YYMMDD。

   2. 如果是3位數字，而且先前已處理到年份資訊的話，那麼它會被解譯為該年的第幾天。

   3. 如果是4位或6位數字，而且先前已處理到年份資訊的話，那麼它會被解譯為時間資訊，對應格式為HHMM或HHMMSS。

   4. 如果是3個或更多的數字，並且先前未處理到日期資訊的話，那麼它會是年份資訊。（這里將直接判斷為yy-mm-dd的日期格式。）

   5. 最後，日期格式將依DateStyle所定義的，設定為：mm-dd-yy，dd-mm-yy，或yy-mm-dd。其中如果月份或日子名稱無法找到合法字詞的話，那將會回傳錯誤的訊息。

4. 如果指定了BC（西元前）的話，那麼實際儲存值將是負的年份數再加1。（陽曆Gregorian year中並無西元0年，所以西元1年，以數值0作為年份的記錄值。）

5. 沒有指定BC的2位數年份，將自動被調整為4位數。其規則為：若小於70，則加2000作為其記錄值，否則就加1900作為記錄值。

小技巧

• 若需要描述西元（Gregorian years AD） 1-99年，請將年份數值以0補滿4位數（例如：0099表西元99年）。

Table B.1. 月份名稱