sql - 如何在Postgres的create index语句中使用嵌套查询的输出

Question

我有一种情况想要在表中添加部分唯一索引。此索引仅适用于行的子集和以后将添加的行。我必须在多个数据库中放置此索引。问题是我必须使用每个数据库表中的数据来确定“where”子句。

以下查询给出错误:

create unique index user_car_unique 
ON car_usage(user_id,car_id) 
where  date_created > 
(select Max(date_created) from car_usage where id > 10)

我得到的错误是:

ERROR: cannot use subquery in index predicate
SQL state: 0A000
Character: 98

但是，以下查询有效:

create unique index user_car_unique 
ON car_usage(user_id,car_id) 
where  date_created > '2014-08-12'

有没有一种方法可以解决“无法在索引谓词中使用子查询”错误的问题？

我的项目使用Grails。我将编写一个数据库迁移(DBM)脚本来添加此索引，该索引将在启动时执行。

我需要这个的原因:

我有一个重复条目的表。由于业务需要，这些条目在那里。要实现它，我必须在表中插入重复项。现在，该要求已更改。 “henceforth”表中不应有重复项，并且旧的重复项应保持原样。为了在数据库层执行该操作，我想施加唯一约束。由于存在旧的重复项，因此我无法施加应用约束。因此，我选择使用部分键。我还有其他方法可以实现这一目标吗？

Answer 1

既然您已经说过这是由于业务需求的变化而导致重复的问题将不再发生，所以我建议将重复的条目移到主表中inherits的存档表中。

然后，除非您使用select ... from only，否则对主表的查询也将下拉到继承的表中，一旦将重复的条目移出存档表，您就可以在主表上有一个常规的唯一索引。

因此，如果您有类似以下内容:

create table foo
(
  table_id serial,
  id int not null,
  val text not null,
  created timestamp without time zone default now() not null
);


insert into foo (id, val, created)
  values (1, 'one', '2014-09-14 09:00:00'),
         (2, 'two', '2014-09-14 11:00:00'),
         (2, 'two', '2014-09-14 12:00:00'),
         (2, 'two', '2014-09-14 13:00:00'),
         (3, 'three', now());


create table foo_archive
(
) inherits (foo);

然后，您可以执行以下操作从主表中删除重复项(假设您要使用某种唯一标识符，该标识符超出了您尝试添加的唯一索引；如果没有，则可以选择如何确定要添加的唯一标识符)。保持-最早创建的时间或类似的时间;无论哪种情况都最适合您的用例，并且您拥有的数据也是如此):

create temp table min_table_ids as
select id, val, min(table_id) as min_table_id
from foo
group by id, val
order by id, val;

insert into foo_archive
select *
from foo
where table_id NOT IN (select min(table_id)
                       from foo
                       group by id, val
                       order by id, val
                      );

delete from only foo
where table_id NOT IN (select min_table_id
                       from min_table_ids
                      );

select *
from only foo
order by id, val;

select *
from foo_archive
order by id, val;

这是相关的 sqlfiddle。

过去，我曾使用这种通用技术来分离不同组的其他相似数据，并发现它工作得很好。

它还具有使主表更小并且易于通过查询通过 select ... from only从查询中拼接出较旧数据或通过常规 select保留较旧数据的便利。

然后，一旦删除了重复项，就可以在主表上启用常规的 unique constraint(而不是 unique index)，即

alter table foo
  add constraint foo_uniq_key unique (id, val);