sql - Create a unique index on a non-unique column

Question

Not sure if this is possible in PostgreSQL 9.3+, but I'd like to create a unique index on a non-unique column. For a table like:

CREATE TABLE data (
  id SERIAL
  , day DATE
  , val NUMERIC
);
CREATE INDEX data_day_val_idx ON data (day, val); 

I'd like to be able to [quickly] query only the distinct days. I know I can use data_day_val_idx to help perform the distinct search, but it seems this adds extra overhead if the number of distinct values is substantially less than the number of rows in the index covers. In my case, about 1 in 30 days is distinct.

Is my only option to create a relational table to only track the unique entries? Thinking:

CREATE TABLE days (
  day DATE PRIMARY KEY
);

And update this with a trigger every time we insert into data.

Answer 1

An index can only index actual rows, not aggregated rows. So, yes, as far as the desired index goes, creating a table with unique values like you mentioned is your only option. Enforce referential integrity with a foreign key constraint from data.day to days.day. This might also be best for performance, depending on the complete situation.

However, since this is about performance, there is an alternative solution: you can use a recursive CTE to emulate a loose index scan:

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT day FROM data ORDER BY 1 LIMIT 1
   )
   UNION ALL
   SELECT (SELECT day FROM data WHERE day > c.day ORDER BY 1 LIMIT 1)
   FROM   cte  c
   WHERE  c.day IS NOT NULL  -- exit condition
   )
SELECT day FROM cte;

Parentheses around the first SELECT are required because of the attached ORDER BY and LIMIT clauses. See:

• Combining 3 SELECT statements to output 1 table

This only needs a plain index on day.

There are various variants, depending on your actual queries:

• Optimize GROUP BY query to retrieve latest row per user
• Unused index in range of dates query
• Select first row in each GROUP BY group?

More in my answer to your follow-up querstion:

• Counting distinct rows using recursive cte over non-distinct index