3 - 資料型態

這裡只列出常用的，欲了解所有資料型態，請見官方文件。

Name	Alias	Description
bigint	int8	signed eight-byte integer
bigserial	serial	autoincrementing eight-byte integer
bit[({n})]	fixed-length bit string
bit varing[({n})]	varbit[({n})]	variable-length bit string
boolean	bool	logical Boolean (true/false)
character[({n})]	char[({n})]	fixed-length character string
character varing[({n})]	varchar[({n})]	variable-length character string
date	calendar date (year, month, day)
double precision	float8	double precision floating-point number (8 bytes)
integer	int, int4	signed four-byte integer
json	textual JSON data
numeric[({p}, {s})]	decimal[({p}, {s})]	exact numeric of selectable precision
real	float4	single precision floating-point number (4 bytes)
smallint	int2	signed two-byte integer
smallserial	serial2	auto-incrementing two-byte integer
serial	serial4	auto-incrementing 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
uuid	universally unique identifier

CHAR(n) vs. VARCHAR(n) vs. TEXT

Data Type	Description
CHARACTER VARYING(n), VARCHAR(n)	variable-length with length limit
CHARACTER(n), CHAR(n)	fixed-length, ==blank padded==
TEXT, VARCHAR	variable unlimited length

自定義資料型態

舉例：

CREATE TYPE gender_type AS ENUM ('M', 'F');

將 column gender 的資料型態改成自定義的型態 gender_type：

ALTER TABLE customer
ALTER COLUMN gender TYPE gender_type USING gender::gender_type;

延伸閱讀

• numeric vs real vs double precision