PG数据库之数据类型入门

PG数据库支持多种数据类型，包括但不限于以下类型：数值类型、字符类型、日期和时间类型、布尔类型、枚举类型、数组类型、JSON类型、几何类型、网络地址类型、UUID类型、XML类型、二进制类型、范围类型、自定义类型等。以下是每种数据类型的详细介绍及其具体示例。

一、数值类型

PG数据库提供了多种数值类型，用于存储整数、浮点数和定点数。

1. 整型数据类型

   • smallint：2字节有符号整数，范围从-32,768到32,767。
     • 示例：CREATE TABLE example (small_int_column smallint);
   • integer（别名：int、int4）：4字节有符号整数，范围从-2,147,483,648到2,147,483,647。
     • 示例：CREATE TABLE example (int_column integer);
   • bigint（别名：int8）：8字节有符号整数，范围从-9,223,372,036,854,775,808到9,223,372,036,854,775,807。
     • 示例：CREATE TABLE example (big_int_column bigint);
   • serial（别名：smallserial、serial4、serial8）：自动递增的整数类型，常用于主键。
     • 示例：CREATE TABLE example (id serial PRIMARY KEY, name text);

2. 浮点型数据类型

   • real：4字节浮点数，精度约为7位十进制数。
     • 示例：CREATE TABLE example (real_column real);
   • double precision：8字节浮点数，精度约为15位十进制数。
     • 示例：CREATE TABLE example (double_precision_column double precision);
   • numeric（别名：decimal）：任意精度的定点数，可以指定精度和小数位数。
     • 示例：CREATE TABLE example (numeric_column numeric(10, 2));

3. 货币类型

   • money：存储带有固定小数精度的货币金额。
     • 示例：CREATE TABLE example (money_column money);

二、字符类型

字符类型用于存储字符串数据。

1. char(n)：固定长度的字符串，长度范围为1到10个字符。如果插入的字符串短于列的长度，PostgreSQL会填充空格。

   • 示例：CREATE TABLE example (char_column char(5));

2. varchar(n)：可变长度的字符串，长度范围为1到1048576个字符。

   • 示例：CREATE TABLE example (varchar_column varchar(50));

3. text：可变长度的字符串，最长可达到1GB的文本数据。

   • 示例：CREATE TABLE example (text_column text);

三、日期和时间类型

日期和时间类型用于存储日期和时间数据。

1. date：日期类型，格式为’YYYY-MM-DD’。

   • 示例：CREATE TABLE example (date_column date);

2. time：时间类型，格式为’HH:MI:SS’或’HH:MM:SS AM’/‘PM’。

   • 示例：CREATE TABLE example (time_column time);

3. timestamp：时间戳类型，表示从’1970-01-01 00:00:00+00’到指定时间的秒数（精确到纳秒）。

   • 示例：CREATE TABLE example (timestamp_column timestamp);

4. interval：间隔类型，表示两个日期或时间之间的时间间隔（以天、小时、分钟、秒为单位）。

   • 示例：CREATE TABLE example (interval_column interval);

四、布尔类型

布尔类型用于存储布尔值。

• boolean：只有两个值：true和false。
  • 示例：CREATE TABLE example (boolean_column boolean);

五、枚举类型

枚举类型是一个包含静态和值的有序集合的数据类型。

• enum：使用CREATE TYPE命令创建枚举类型。
  • 示例：CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
  • 使用：CREATE TABLE example (mood_column mood);

六、数组类型

数组类型可以存储多个数值、文本或其他数据类型的数组。

• array：可以是任何基本类型或用户定义类型，枚举类型或复合类型。
  • 示例：CREATE TABLE example (array_column integer[]);
  • 示例：CREATE TABLE example (multidimensional_array text[][]);

七、JSON类型

JSON类型用于存储JSON格式的数据。

• json：存储JSON数据，插入数据时会自动检测字符串是否为合法的JSON数据。
  • 示例：CREATE TABLE example (json_column json);
• jsonb：存储JSON数据，但在存储时就把JSON解析为二进制格式，使用时无须再次解析，性能更高。
  • 示例：CREATE TABLE example (jsonb_column jsonb);

八、几何类型

几何类型用于存储二维的平面物体。

• point：表示一个点。
  • 示例：CREATE TABLE example (point_column point);
• line：表示一条线。
  • 示例：CREATE TABLE example (line_column line);
• lseg：表示一条线段。
  • 示例：CREATE TABLE example (lseg_column lseg);
• box：表示一个矩形框。
  • 示例：CREATE TABLE example (box_column box);
• path：表示一个路径。
  • 示例：CREATE TABLE example (path_column path);
• polygon：表示一个多边形。
  • 示例：CREATE TABLE example (polygon_column polygon);
• circle：表示一个圆。
  • 示例：CREATE TABLE example (circle_column circle);

九、网络地址类型

网络地址类型用于存储IP地址和MAC地址。

• inet：用于存储IPv4和IPv6地址。
  • 示例：CREATE TABLE example (inet_column inet);
• cidr：用于存储网络地址。
  • 示例：CREATE TABLE example (cidr_column cidr);
• macaddr：用于存储MAC地址。
  • 示例：CREATE TABLE example (macaddr_column macaddr);

十、UUID类型

UUID类型用于存储通用唯一标识符。

• uuid：存储RFC 4122,ISO/IEF 9834-8:2005及相关标准定义的UUID。
  • 示例：CREATE TABLE example (uuid_column uuid);

十一、XML类型

XML类型用于存储XML数据。

• xml：存储XML数据，插入数据时会对输入的数据进行检查，使不符合XML标准的数据不能存放到数据库中。
  • 示例：CREATE TABLE example (xml_column xml);

十二、二进制类型

二进制类型用于存储二进制数据。

• bytea：存储二进制数据。
  • 示例：CREATE TABLE example (bytea_column bytea);

十三、范围类型

范围类型用于存储范围数据。

• range：可以是任何数值类型、日期时间类型或时间间隔类型的范围。
  • 示例：CREATE TABLE example (range_column int4range);

十四、自定义类型

用户可以通过CREATE TYPE语句创建自定义数据类型。

• 自定义类型：根据实际需求创建新的数据类型。
  • 示例：CREATE TYPE address AS (street text, city text, zipcode text);
  • 使用：CREATE TABLE example (address_column address);

十五、综合示例

下面是一个综合使用多种数据类型的示例。

CREATE TABLE user (  id SERIAL PRIMARY KEY, -- 主键ID，自增序列（SERIAL类型）  name VARCHAR(50) NOT NULL, -- 用户名，长度为50个字符，不能为空（VARCHAR类型）  email VARCHAR(100) NOT NULL, -- 邮箱，长度为100个字符，不能为空（VARCHAR类型）  age INTEGER, -- 年龄，整数类型（INTEGER类型）  birthdate DATE, -- 出生日期，日期类型（DATE类型）  registration_time TIMESTAMPTZ DEFAULT current_timestamp, -- 注册时间，带时区的时间戳类型，默认为当前时间戳（TIMESTAMPTZ类型）  is_active BOOLEAN DEFAULT TRUE, -- 是否活跃，布尔类型，默认为TRUE（BOOLEAN类型）  preferences JSONB, -- 用户偏好，JSONB类型  uuid_field UUID DEFAULT uuid_generate_v4(), -- UUID字段，默认为生成的UUID（UUID类型）  ip_address INET -- IP地址，INET类型  
);

十六、总结

PostgreSQL（PG）数据库支持多样的数据类型，涵盖整数、浮点数、任意精度数值的数值类型，字符、文本字符串的字符类型，以及日期、时间、时间戳的日期和时间类型。此外，它还支持布尔、二进制数据、数组、JSON/JSONB、UUID、网络地址、几何数据类型等。这些丰富的数据类型使得PostgreSQL在数据存储和处理上具有高度的灵活性和精确性。