PostgreSQL 唯一索引排除 NULL|极客教程

PostgreSQL 唯一索引排除 NULL

概述

数据库索引是一种提高查询效率的重要工具。在 PostgreSQL 中，唯一索引是一种特殊的索引，它要求被索引的列的值在整个表中是唯一的。然而，当存在 NULL 值时，唯一索引会变得复杂，因为 NULL 不等于任何值，所以 PostgreSQL 默认情况下将 NULL 视为唯一值。本文将详细介绍 PostgreSQL 中如何通过唯一索引排除 NULL 值。

唯一索引和 NULL

在理解如何排除 NULL 值之前，让我们先了解唯一索引和 NULL 值之间的关系。

唯一索引

唯一索引是一种要求被索引的列（或多个列）的值在整个表中是唯一的索引。它可以防止插入或更新数据时出现重复的索引值。如果创建了唯一索引，那么数据库引擎会自动对索引列进行检查，确保它们的值在整个表中是唯一的。

NULL 值

在数据库中，NULL 是表示缺失值或未知值的特殊值。NULL 不等于任何值，包括它本身。这意味着如果某个列包含 NULL 值，它与其他任何非 NULL 值都不相等。

唯一索引排除 NULL

PostgreSQL 提供了几种方法来创建唯一索引并将 NULL 值排除在外。以下是常用的两种方法：

方法一：部分索引

部分索引是一种在索引中筛选特定行的索引。通过创建一个过滤条件，部分索引只会包含满足条件的行。为了排除 NULL 值，我们可以使用部分索引来创建一个不包含 NULL 值的唯一索引。

下面是一个示例，创建了一个名为 users 的表，并在 email 列上创建了一个不包含 NULL 值的唯一索引：

CREATE TABLE users (
    id serial PRIMARY KEY,
    name varchar(100),
    email varchar(100)
);

CREATE UNIQUE INDEX unique_email ON users(email)
WHERE email IS NOT NULL;

在上面的示例中，我们使用 WHERE email IS NOT NULL 过滤条件来创建了一个部分索引。这样，唯一索引 unique_email 将不会包含 NULL 值。

方法二：函数索引

函数索引是一种基于函数返回值的索引。通过在索引中存储函数的计算结果，函数索引可以提高查询的效率。为了排除 NULL 值，我们可以使用函数索引来创建一个不包含 NULL 值的唯一索引。

以下是一个示例，创建了一个名为 users 的表，并在 email 列上创建了一个不包含 NULL 值的唯一索引：

CREATE TABLE users (
    id serial PRIMARY KEY,
    name varchar(100),
    email varchar(100)
);

CREATE UNIQUE INDEX unique_email ON users(coalesce(email, '')) 
WHERE email IS NOT NULL;

在上面的示例中，我们使用了 coalesce(email, '') 函数来将 NULL 值转换为空字符串，并基于转换结果创建了一个函数索引。这样，唯一索引 unique_email 将不会包含 NULL 值。