Simon Riggs <simon@2ndquadrant.com> writes: > I had understood the discussion to conclude that indexes that do not > depend on any column of the table to not be allowed at all. That was my first reaction too, but the point about unique-index behavior refutes it. Constraining a table to have at most one row is useful. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Simon Riggs <simon@2ndquadrant.com> writes: > On Fri, 2007-11-09 at 04:05 -0500, Tom Lane wrote: >> That was my first reaction too, but the point about unique-index behavior >> refutes it. Constraining a table to have at most one row is useful. > Sure is, and I've done it just a few days ago. > This SQL does it using standard syntax: > create table foo (handle integer primary key check (handle = 1)); That does not constrain the table to have only one row. It constrains it to have only one value of the handle field (thereby making the field useless). The fact that there are workarounds isn't a reason to not support the index option. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Simon Riggs <simon@2ndquadrant.com> writes: > Constants in indexes are just a strangeness we don't need. I disagree. Here's an example that I don't think you can do with purely SQL-spec syntax: CREATE UNIQUE INDEX fooi ON foo ((1)) WHERE f1 < 0; This constrains the table to contain no more than one row with negative f1. Now admittedly this index does depend on the column f1 so it's not directly an example of the case being patched, but I think it would be pretty weird to allow this but reject the base case without a WHERE clause. I also think that there's no principled reason to reject CREATE INDEX fooi ON foo ((1)); if we allow CREATE INDEX fooi ON foo ((CASE WHEN false THEN f1 ELSE 1 END)); The second index is certainly without rational use, but on what grounds will you argue that it's more valid than the other? Basically, my view is that this may be an edge case, but it's not utterly useless, and throwing an error for it will violate the principle of least surprise. It's not hugely different from the reasoning that led us to allow zero-column tables. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

Simon Riggs <simon@2ndquadrant.com> writes: > My only fear is your reminder at a later date that we can't add feature > X because of constant indexes and 8.4 is going to be all about indexes. Well, if there's actually a reason to forbid it at some point, we can reconsider. Right now there is no such reason. Furthermore, I rather imagine that if there were a reason, the restriction it would require would be a bit different than the one under discussion here. If there's something we want to do that cannot work with index expressions that happen to be constants, it seems unlikely that adding a partial index predicate would suddenly make it start to work. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq