Postgresql 中文操作指南

Chapter 8. Data Types

Table of Contents

PostgreSQL 具有提供给用户的各种本机数据类型。用户可以使用 CREATE TYPE 命令向 PostgreSQL 中添加新类型。

PostgreSQL has a rich set of native data types available to users. Users can add new types to PostgreSQL using the CREATE TYPE command.

Table 8.1 显示所有内置的通用数据类型。“别名”列中列出的大多数其他名称都是 PostgreSQL 出于历史原因在内部使用的名称。此外，还有一些内部使用或已弃用的类型可用，但此处未列出。

Table 8.1 shows all the built-in general-purpose data types. Most of the alternative names listed in the “Aliases” column are the names used internally by PostgreSQL for historical reasons. In addition, some internally used or deprecated types are available, but are not listed here.

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 [ (_n) ]_

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

PostgreSQL Log Sequence Number

pg_snapshot

user-level transaction ID snapshot

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 (deprecated; see pg_snapshot)

uuid

universally unique identifier

xml

XML data

Compatibility

SQL 指定了以下类型（或其拼写）： bigint 、 bit 、 bit varying 、 boolean 、 char 、 character varying 、 character 、 varchar 、 date 、 double precision 、 integer 、 interval 、 numeric 、 decimal 、 real 、 smallint 、 time （带时区或不带时区）、 timestamp （带时区或不带时区）、 xml 。

The following types (or spellings thereof) are specified by 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 独有的，例如几何路径，要么有几种可能的格式，例如日期和时间类型。部分输入和输出函数无法反转，即与原始输入相比，输出函数的结果可能会丢失精度。

Each data type has an external representation determined by its input and output functions. Many of the built-in types have obvious external formats. However, several types are either unique to PostgreSQL, such as geometric paths, or have several possible formats, such as the date and time types. Some of the input and output functions are not invertible, i.e., the result of an output function might lose accuracy when compared to the original input.