Postgres约束确保存在多列？ [英] Postgres constraint ensuring one column of many is present?

问题描述

有什么好方法可以向PostgreSQL添加约束来检查（从一组列中）确切地一个列包含非空值？

What are good ways to add a constraint to PostgreSQL to check that exactly one column (from a set of columns) contains a non-null value?

Update ：很可能我想使用 check 表达式/9.2/static/sql-altertable.html>创建表和更改表。

Update: It is likely that I want to use a check expression as detailed in Create Table and Alter Table.

更新：我正在浏览可用的功能。

Update: I'm looking through the available functions.

更新：仅出于背景考虑，这是Rails验证我当前正在使用的逻辑：

Update: Just for background, here is the Rails validation logic I'm currently using:

validate :multi_column_validation
def multi_column_validation
  n = 0
  n += 1 if column_1
  n += 1 if column_2
  n += 1 if column_3
  unless 1 == n
    errors.add(:base, "Exactly one column from " +
      "column_1, column_2, column_3 must be present")
  end
end

在这里寻找PSQL，而不是Ruby。我只是想展示我正在使用的逻辑，因为它比枚举所有真值表可能性更紧凑。

To be clear, I'm looking for PSQL, not Ruby, here. I just wanted to show the logic I'm using since it is more compact than enumerating all "truth table" possibilities.

推荐答案

我认为最干净和通用的解决方案是创建一个函数来计算某些参数的空值。为此，您可以使用伪-键入 anyarray 和类似这样的SQL函数：

I think the most clean and generic solution is to create a function to count the null values from some arguments. For that you can use the pseudo-type anyarray and a SQL function like that:

CREATE FUNCTION count_not_nulls(p_array anyarray)
RETURNS BIGINT AS
$$
    SELECT count(x) FROM unnest($1) AS x
$$ LANGUAGE SQL IMMUTABLE;

使用该功能，您可以创建检查约束 as：

With that function, you can create your CHECK CONSTRAINT as:

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK(count_not_nulls(array[col1, col2, col3]) = 1);

这仅在列具有相同数据类型时才有效。如果不是这种情况，则可以将其转换为文本，例如作为文本（因为您只关心null情况）：

This will only work if the columns are of the same data type. If it's not the case, you can cast them, as text for instance (as you just care for the null case):

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK(count_not_nulls(array[col1::text, col2::text, col3::text]) = 1);

正如@muistooshort所记住的那样，您可以使用各种参数，可以清楚地调用：

As well remembered by @muistooshort, you can create the function with variadic arguments, which makes it clear to call:

CREATE FUNCTION count_not_nulls(variadic p_array anyarray)
RETURNS BIGINT AS
$$
    SELECT count(x) FROM unnest($1) AS x
$$ LANGUAGE SQL IMMUTABLE;

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK(count_not_nulls(col1, col2, col3) = 1);

这篇关于Postgres约束确保存在多列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！