如何在 postgres 上的 create index 语句中使用嵌套查询的输出 [英] How do I use output of a nested query in create index statement on postgres

问题描述

我想在表中添加部分唯一索引的情况.此索引必须仅应用于行和行的子集，这些行和行将在以后添加.我必须在多个数据库中放置此索引.问题是我必须使用每个数据库的表中的数据来确定'where'子句.

I have a situation where I want to add partial unique index in a table. This index has to be applied only to a subset of rows and rows that will be added henceforth. There are multiple databases where I have to put this index. The problem is that I have to use the data in the table of each database to determine the 'where' clause.

以下查询出错:

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'

有什么办法可以解决不能在索引谓词中使用子查询"错误?

Is there a way I can get around the "cannot use subquery in index predicate" error?

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

My project uses Grails. I will be writing a database migration (DBM) script to add this index which will execute during the start up time.

我需要这个的原因:

我有一个包含重复条目的表格.这些条目是由于业务需求而存在的.为了实现它，我必须在表中插入重复项.现在这个要求改变了.此后"表中不应有重复项，旧的重复项应保持原样.为了在数据库层强制执行，我想放置唯一约束.我不能施加应用约束，因为有旧的重复项.因此我选择使用部分键.有没有其他方法可以实现这一目标?

I have a table with duplicate entries. These entries are there because of a business requirement. To implement it I had to insert duplicates in the table. Now that requirement is changed. There should be NO duplicates in the table 'henceforth' and the old duplicates should remain as they are. To enforce that at the DB layer I want to put unique constraint. I cannot put apply constraint as there are old duplicates. Hence I opted to use partial keys. Is there any other way I can achieve this?

推荐答案

既然你已经说明这是由于业务需求的变化，并且导致重复的问题将不再发生，我建议改为移动将条目复制到存档表中，该存档表继承来自主表.

Since you've stated that this is due to a change in business requirements and the issue that causes the duplicates will no longer occur, I would suggest instead moving the duplicate entries into an archive table which inherits from the main table.

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

Then queries against the main table will also drop down into the inherited table, unless you use select ... from only, and you can have a regular unique index on the main table once you move the duplicate entries out into the archive table.

所以如果你有类似的东西:

So if you had something like:

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);

然后你可以做这样的事情来从主表中删除重复项(假设你有某种唯一标识符可以使用，它超出了你试图添加的唯一索引；如果没有，你可以选择如何决定要保留哪个 - 最早的创建时间，或类似的东西；无论哪种最适合您的用例和您拥有的数据):

You could then do something like this to remove the duplicates from the main table (assuming you have some sort of unique identifier to use that goes beyond the unique index you were trying to add; if not, you can choose how to decide which one to keep -- earliest created time, or something of that nature; whatever fits your use case best with the data you have):

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.

我过去曾使用这种通用技术来分离不同组的其他相似数据，并发现它的效果非常好.

I have used this general technique in the past to separate different groups of otherwise similar data and find that it works pretty well.

它还具有使您的主表更小并且易于从查询中拼接旧数据的附带好处，如果您愿意，可以通过 select ... from only 或保留旧数据通过常规的select.

It also has the side benefit of making your main table smaller and easy to splice the older data out of a query if you want to, via select ... from only or keep the older data via a regular select.

然后，一旦删除了重复项，就可以在主表上启用常规的唯一约束(而不是唯一索引)，即

Then, once you have the duplicates removed, you can enable a regular unique constraint (instead of a unique index) on the main table, i.e.

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

这篇关于如何在 postgres 上的 create index 语句中使用嵌套查询的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！