8. 資料型別

PostgreSQL 內建一套豐富的資料型別供用戶使用。使用者也可以使用 CREATE TYPE 指令讓 PostgreSQL 增加新的資料型別。
Table 8.1 列出所有內建的通用資料型別。大多數列在「Aliases」中的替代名稱是由於在 PostgreSQL 內部使用的歷史因素。此外，還有一些內部使用或不建議使用的資料型別，但這裡並沒有列出。
Table 8.1. Data Types
Name
Aliases
Description
bigint
int8
signed eight-byte integer
bigserial
serial8
autoincrementing eight-byte integer
bit [ (n) ]
​
fixed-length bit string
bit varying [ (n) ]
varbit
variable-length bit string
boolean
bool
logical Boolean (true/false)
box
​
rectangular box on a plane
bytea
​
binary data (“byte array”)
character [ (n) ]
char [ (n) ]
fixed-length character string
character varying [ (n) ]
varchar [ (n) ]
variable-length character string
cidr
​
IPv4 or IPv6 network address
circle
​
circle on a plane
date
​
calendar date (year, month, day)
double precision
float8
double precision floating-point number (8 bytes)
inet
​
IPv4 or IPv6 host address
integer
int,int4
signed four-byte integer
interval [fields] [ (p) ]
​
time span
json
​
textual JSON data
jsonb
​
binary JSON data, decomposed
line
​
infinite line on a plane
lseg
​
line segment on a plane
macaddr
​
MAC (Media Access Control) address
macaddr8
​
MAC (Media Access Control) address (EUI-64 format)
money
​
currency amount
numeric [ (p,s) ]
decimal [ (p,s) ]
exact numeric of selectable precision
path
​
geometric path on a plane
pg_lsn
​
PostgreSQLLog Sequence Number
point
​
geometric point on a plane
polygon
​
closed geometric path on a plane
real
float4
single precision floating-point number (4 bytes)
smallint
int2
signed two-byte integer
smallserial
serial2
autoincrementing two-byte integer
serial
serial4
autoincrementing four-byte integer
text
​
variable-length character string
time [ (p) ] [ without time zone ]
​
time of day (no time zone)
time [ (p) ] with time zone
timetz
time of day, including time zone
timestamp [ (p) ] [ without time zone ]
​
date and time (no time zone)
timestamp [ (p) ] with time zone
timestamptz
date and time, including time zone
tsquery
​
text search query
tsvector
​
text search document
txid_snapshot
​
user-level transaction ID snapshot
uuid
​
universally unique identifier
xml
​
XML data
相容性
以下資料型別（或其拼寫方式）是由 SQL 指定的：bigint,bit,bit varying,boolean,char,character varying,character,varchar,date,double precision,integer,interval,numeric,decimal,real,smallint,time(with or without time zone),timestamp(with or without time zone),xml.
每種資料型別都具有其明確的輸入和輸出功能外部表示法。許多內建的資料型別都有明顯的外部格式。但是，有幾種資料型別是 PostgreSQL 獨有的，比如幾何路徑，或者有幾種可能的格式，像是日期和時間型別。某些輸入和輸出功能是不可逆的，意即，與原始輸入相比，輸出功能的結果可能會失去一些精確度。

7.8. WITH Querys（Common Table Expressions）

8.1. 數字型別

Last updated 3 years ago

Name	Aliases	Description
`bigint`	`int8`	signed eight-byte integer
`bigserial`	`serial8`	autoincrementing eight-byte integer
`bit [ (n`) ]		fixed-length bit string
`bit varying [ (n`) ]	`varbit`	variable-length bit string
`boolean`	`bool`	logical Boolean (true/false)
`box`		rectangular box on a plane
`bytea`		binary data (“byte array”)
`character [ (n`) ]	`char [ (n`) ]	fixed-length character string
`character varying [ (n`) ]	`varchar [ (n`) ]	variable-length character string
`cidr`		IPv4 or IPv6 network address
`circle`		circle on a plane
`date`		calendar date (year, month, day)
`double precision`	`float8`	double precision floating-point number (8 bytes)
`inet`		IPv4 or IPv6 host address
`integer`	`int`,`int4`	signed four-byte integer
`interval [fields`] [ (`p`) ]		time span
`json`		textual JSON data
`jsonb`		binary JSON data, decomposed
`line`		infinite line on a plane
`lseg`		line segment on a plane
`macaddr`		MAC (Media Access Control) address
`macaddr8`		MAC (Media Access Control) address (EUI-64 format)
`money`		currency amount
`numeric [ (p`,`s`) ]	`decimal [ (p`,`s`) ]	exact numeric of selectable precision
`path`		geometric path on a plane
`pg_lsn`		PostgreSQLLog Sequence Number
`point`		geometric point on a plane
`polygon`		closed geometric path on a plane
`real`	`float4`	single precision floating-point number (4 bytes)
`smallint`	`int2`	signed two-byte integer
`smallserial`	`serial2`	autoincrementing two-byte integer
`serial`	`serial4`	autoincrementing four-byte integer
`text`		variable-length character string
`time [ (p`) ] [ without time zone ]		time of day (no time zone)
`time [ (p`) ] with time zone	`timetz`	time of day, including time zone
`timestamp [ (p`) ] [ without time zone ]		date and time (no time zone)
`timestamp [ (p`) ] with time zone	`timestamptz`	date and time, including time zone
`tsquery`		text search query
`tsvector`		text search document
`txid_snapshot`		user-level transaction ID snapshot
`uuid`		universally unique identifier
`xml`		XML data