PostgreSQL 表创建和数据类型

介绍

在这个实验（Lab）中，我们将探索 PostgreSQL 表的创建和数据类型。目标是理解基本的数据类型，如整数、文本、日期和布尔值，这些对于定义表结构和确保数据完整性至关重要。

我们将使用 psql 连接到 PostgreSQL 数据库，使用 SERIAL 创建带有主键的表，并添加基本的约束，例如 NOT NULL 和 UNIQUE。然后，我们将检查表结构并插入数据，以演示不同数据类型（如 INTEGER、SMALLINT、TEXT、VARCHAR(n) 和 CHAR(n)）的用法。

探索 PostgreSQL 数据类型

在这一步中，我们将探索 PostgreSQL 中可用的一些基本数据类型。理解数据类型对于定义表结构和确保数据完整性至关重要。我们将介绍常见的类型，如整数、文本、日期和布尔值。

首先，让我们连接到 PostgreSQL 数据库。打开一个终端，并使用 psql 命令以 postgres 用户身份连接到 postgres 数据库。由于 postgres 用户是默认的超级用户，你可能需要先使用 sudo 切换到该用户。

sudo -u postgres psql

现在你应该在 PostgreSQL 交互式终端中。你将看到一个类似 postgres=# 的提示符。

现在，让我们探索一些基本的数据类型。

1. 整数类型（Integer Types）：

PostgreSQL 提供了几种具有不同范围的整数类型。最常见的是 INTEGER（或 INT）和 SMALLINT。

• INTEGER：对于大多数整数值来说，这是一个典型的选择。
• SMALLINT：用于较小的整数值，以节省空间。

让我们创建一个简单的表来演示这些类型：

CREATE TABLE integer_example (
    id SERIAL PRIMARY KEY,
    quantity INTEGER,
    small_quantity SMALLINT
);

在这里，SERIAL 是一种特殊的类型，它可以自动生成一个整数序列，使其适合作为主键。

现在，插入一些数据：

INSERT INTO integer_example (quantity, small_quantity) VALUES (100, 10);
INSERT INTO integer_example (quantity, small_quantity) VALUES (2000000, 32767);

你可以使用以下命令查看数据：

SELECT * FROM integer_example;

输出：

 id | quantity | small_quantity
----+----------+----------------
  1 |      100 |             10
  2 |  2000000 |          32767
(2 rows)

2. 文本类型（Text Types）：

PostgreSQL 提供了 TEXT、VARCHAR(n) 和 CHAR(n) 用于存储文本。

• TEXT：存储无限长度的可变长度字符串。
• VARCHAR(n)：存储最大长度为 n 的可变长度字符串。
• CHAR(n)：存储长度为 n 的固定长度字符串。如果字符串较短，则用空格填充。

让我们创建另一个表：

CREATE TABLE text_example (
    id SERIAL PRIMARY KEY,
    name TEXT,
    short_name VARCHAR(50),
    code CHAR(5)
);

插入一些数据：

INSERT INTO text_example (name, short_name, code) VALUES ('PostgreSQL Database', 'PostgreSQL', 'PG001');
INSERT INTO text_example (name, short_name, code) VALUES ('Another Database', 'Another', 'AD002');

查看数据：

SELECT * FROM text_example;

输出：

 id |        name        | short_name | code
----+--------------------+------------+-------
  1 | PostgreSQL Database | PostgreSQL | PG001
  2 | Another Database   | Another    | AD002
(2 rows)

3. 日期和时间类型（Date and Time Types）：

PostgreSQL 提供了 DATE、TIME、TIMESTAMP 和 TIMESTAMPTZ 用于处理日期和时间值。

• DATE：仅存储日期（年、月、日）。
• TIME：仅存储时间（时、分、秒）。
• TIMESTAMP：存储日期和时间，不包含时区信息。
• TIMESTAMPTZ：存储日期和时间，包含时区信息。

创建表：

CREATE TABLE datetime_example (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP,
    event_timestamptz TIMESTAMPTZ
);

插入数据：

INSERT INTO datetime_example (event_date, event_time, event_timestamp, event_timestamptz)
VALUES ('2023-10-27', '10:30:00', '2023-10-27 10:30:00', '2023-10-27 10:30:00+00');

查看数据：

SELECT * FROM datetime_example;

输出：

 id | event_date | event_time |   event_timestamp   |    event_timestamptz
----+------------+------------+---------------------+----------------------------
  1 | 2023-10-27 | 10:30:00   | 2023-10-27 10:30:00 | 2023-10-27 10:30:00+00
(1 row)

4. 布尔类型（Boolean Type）：

BOOLEAN 类型存储真/假值。

创建表：

CREATE TABLE boolean_example (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN
);

插入数据：

INSERT INTO boolean_example (is_active) VALUES (TRUE);
INSERT INTO boolean_example (is_active) VALUES (FALSE);

查看数据：

SELECT * FROM boolean_example;

输出：

 id | is_active
----+-----------
  1 | t
  2 | f
(2 rows)

最后，退出 psql 终端：

\q

你现在已经探索了 PostgreSQL 中的一些基本数据类型。这些数据类型构成了创建健壮且定义良好的数据库模式的基础。

创建带有主键的表

在这一步中，我们将学习如何在 PostgreSQL 中创建带有主键的表。主键是表中的一列或一组列，用于唯一标识表中的每一行。它强制唯一性，并且是数据完整性和表之间关系的关键要素。

首先，让我们连接到 PostgreSQL 数据库。打开一个终端，并使用 psql 命令以 postgres 用户身份连接到 postgres 数据库。

sudo -u postgres psql

现在你应该在 PostgreSQL 交互式终端中。

理解主键（Understanding Primary Keys）

主键具有以下特征：

• 它必须包含唯一的值。
• 它不能包含 NULL 值。
• 一个表只能有一个主键。

创建带有主键的表（Creating a Table with a Primary Key）

创建表时，有两种常见的方法来定义主键：

1. 在列定义中使用 PRIMARY KEY 约束：

   CREATE TABLE products (
       product_id SERIAL PRIMARY KEY,
       product_name VARCHAR(100),
       price DECIMAL(10, 2)
   );
   

   在这个例子中，product_id 被定义为主键，使用了 PRIMARY KEY 约束。SERIAL 关键字自动创建一个序列，为 product_id 生成唯一的整数值。

2. 单独使用 PRIMARY KEY 约束：

   CREATE TABLE customers (
       customer_id INT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       PRIMARY KEY (customer_id)
   );
   

   在这里，PRIMARY KEY 约束是单独定义的，指定 customer_id 列为主键。

示例：创建带有主键的 users 表

让我们创建一个带有主键的 users 表，使用 SERIAL 类型自动生成 ID：

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    registration_date DATE
);

在这个表中：

• user_id 是主键，使用 SERIAL 自动生成。
• username 是每个用户的唯一用户名。
• email 是用户的电子邮件地址。
• registration_date 是用户注册的日期。

现在，让我们向 users 表中插入一些数据：

INSERT INTO users (username, email, registration_date) VALUES ('john_doe', 'john.doe@example.com', '2023-10-26');
INSERT INTO users (username, email, registration_date) VALUES ('jane_smith', 'jane.smith@example.com', '2023-10-27');

你可以使用以下命令查看数据：

SELECT * FROM users;

输出：

 user_id |  username  |        email        | registration_date
---------+------------+---------------------+---------------------
       1 | john_doe   | john.doe@example.com | 2023-10-26
       2 | jane_smith | jane.smith@example.com | 2023-10-27
(2 rows)

尝试插入重复的主键

如果你尝试插入一行具有重复主键的行，PostgreSQL 将引发一个错误：

INSERT INTO users (user_id, username, email, registration_date) VALUES (1, 'duplicate_user', 'dup@example.com', '2023-10-28');

输出：

ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (user_id)=(1) already exists.

这演示了主键约束的实际作用，防止重复值。

最后，退出 psql 终端：

\q

你现在已经成功创建了一个带有主键的表，并观察了它是如何强制唯一性的。这是数据库设计中的一个基本概念。

添加基本约束 (NOT NULL, UNIQUE)

在这一步中，我们将学习如何在 PostgreSQL 中向表添加基本约束。约束是强制数据完整性和一致性的规则。我们将重点介绍两个基本约束：NOT NULL 和 UNIQUE。

首先，让我们连接到 PostgreSQL 数据库。打开一个终端，并使用 psql 命令以 postgres 用户身份连接到 postgres 数据库。

sudo -u postgres psql

现在你应该在 PostgreSQL 交互式终端中。

理解约束（Understanding Constraints）

约束用于限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。

1. NOT NULL 约束

NOT NULL 约束确保列不能包含 NULL 值。当特定信息对于表中的每一行都至关重要时，这非常有用。

2. UNIQUE 约束

UNIQUE 约束确保列中的所有值都是不同的。这对于应该具有唯一标识符或值的列（例如用户名或电子邮件地址，主键除外）非常有用。

在创建表时添加约束（Adding Constraints During Table Creation）

你可以在创建表时添加约束。让我们创建一个名为 employees 的表，其中包含 NOT NULL 和 UNIQUE 约束：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

在这个表中：

• employee_id 是主键。
• first_name 和 last_name 被声明为 NOT NULL，这意味着每个员工都必须有值。
• email 被声明为 UNIQUE，确保每个员工都有一个唯一的电子邮件地址。

现在，让我们尝试插入一些违反这些约束的数据。

尝试将 NULL 值插入到 NOT NULL 列中：

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES (NULL, 'Smith', 'john.smith@example.com', '2023-10-27');

输出：

ERROR:  null value in column "first_name" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (1, null, Smith, john.smith@example.com, 2023-10-27).

此错误表明由于 NOT NULL 约束，你无法将 NULL 值插入到 first_name 列中。

尝试将重复值插入到 UNIQUE 列中：

首先，插入一个有效行：

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Smith', 'john.smith@example.com', '2023-10-27');

现在，尝试插入另一个具有相同电子邮件的行：

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', 'john.smith@example.com', '2023-10-28');

输出：

ERROR:  duplicate key value violates unique constraint "employees_email_key"
DETAIL:  Key (email)=(john.smith@example.com) already exists.

此错误表明由于 UNIQUE 约束，你无法插入重复的电子邮件地址。

插入有效数据：

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', 'jane.doe@example.com', '2023-10-28');

查看数据：

SELECT * FROM employees;

输出：

 employee_id | first_name | last_name |        email        | hire_date
-------------+------------+-----------+---------------------+------------
           1 | John       | Smith     | john.smith@example.com | 2023-10-27
           2 | Jane       | Doe       | jane.doe@example.com  | 2023-10-28
(2 rows)

最后，退出 psql 终端：

\q

你现在已经成功创建了一个带有 NOT NULL 和 UNIQUE 约束的表，并观察了它们如何强制数据完整性。

检查表结构

在这一步中，我们将学习如何在 PostgreSQL 中检查表的结构。理解表的结构，包括列名、数据类型、约束和索引，对于有效地查询和操作数据至关重要。

首先，让我们连接到 PostgreSQL 数据库。打开一个终端，并使用 psql 命令以 postgres 用户身份连接到 postgres 数据库。

sudo -u postgres psql

现在你应该在 PostgreSQL 交互式终端中。

\d 命令

在 psql 中检查表结构的主要工具是 \d（describe，描述）命令。此命令提供有关表的详细信息，包括：

• 列名和数据类型
• 约束（主键、唯一约束、非空约束）
• 索引

检查 employees 表

让我们检查一下在上一步中创建的 employees 表的结构：

\d employees

输出：

                                           Table "public.employees"
   Column    |          Type          | Collation | Nullable |                    Default
-------------+------------------------+-----------+----------+------------------------------------------------
 employee_id | integer                |           | not null | nextval('employees_employee_id_seq'::regclass)
 first_name  | character varying(50)  |           | not null |
 last_name   | character varying(50)  |           | not null |
 email       | character varying(100) |           |          |
 hire_date   | date                   |           |          |
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)

输出提供以下信息：

• Table "public.employees": 指示表名和模式（schema）。
• Column: 列出列名（employee_id、first_name、last_name、email、hire_date）。
• Type: 显示每列的数据类型（integer、character varying、date）。
• Nullable: 指示列是否可以包含 NULL 值（not null 或空白）。
• Default: 显示列的默认值（如果有）。
• Indexes: 列出在表上定义的索引，包括主键（employees_pkey）和 email 列上的唯一约束（employees_email_key）。

检查其他表

你可以使用 \d 命令来检查数据库中的任何表。例如，要检查在步骤 2 中创建的 users 表：

\d users

输出：

                                            Table "public.users"
      Column       |          Type          | Collation | Nullable |                Default
-------------------+------------------------+-----------+----------+----------------------------------------
 user_id           | integer                |           | not null | nextval('users_user_id_seq'::regclass)
 username          | character varying(50)  |           |          |
 email             | character varying(100) |           |          |
 registration_date | date                   |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)

列出所有表

要列出当前数据库中的所有表，你可以使用 \dt 命令：

\dt

输出（将根据你创建的表而有所不同）：

              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | boolean_example  | table | postgres
 public | customers        | table | postgres
 public | datetime_example | table | postgres
 public | employees        | table | postgres
 public | integer_example  | table | postgres
 public | products         | table | postgres
 public | text_example     | table | postgres
 public | users            | table | postgres
(8 rows)

最后，退出 psql 终端：

\q

你现在已经学会了如何使用 \d 和 \dt 命令在 PostgreSQL 中检查表的结构。这是理解和使用数据库的基本技能。

总结

在这个实验中，我们探索了 PostgreSQL 的基本数据类型，重点关注整数和文本。我们学习了用于存储整数值的 INTEGER 和 SMALLINT，理解了它们不同的范围和用例。我们还研究了用于处理文本数据的 TEXT、VARCHAR(n) 和 CHAR(n)，注意到了变长字符串和定长字符串之间的区别。

此外，我们还练习了使用这些数据类型创建表，包括使用 SERIAL 自动生成主键序列。我们将示例数据插入到表中，并使用 SELECT 语句验证了数据，从而巩固了我们对这些数据类型在实际数据库环境中如何工作的理解。