CODEWITHSUNDEEP
sqlpostgresqlindexing
PreciousBodilyFluids
PreciousBodilyFluids

Reputation: 12011

Including multiple columns in a single index in Postgres

I have a 'users' table with two columns, 'email' and 'new_email'. I need:

I know that I could do this more easily by creating a related 'emails' table, but I'm expecting to be looking up users in this table (by primary key) from several applications, and I'd like to avoid duplicating the join logic in various places to also retrieve their emails. So I think some kind of expression index would be best, if that's possible.

If this isn't possible, I suppose my next best option would be to create a view that the other applications could use to easily fetch a user's emails along with their other information, but I'm not sure how to do that either.

I'm using Postgres 8.4. Thank you!

Upvotes: 3

Views: 1680

Answers (2)

Scott Marlowe
Scott Marlowe

Reputation: 8920

No need for triggers. Try this:

create  table et (email text, email2 text);
create unique index et_u on et (coalesce(lower(email),lower(email2)));
insert into et (email,email2) values ('[email protected]',NULL);
insert into et (email,email2) values ('[email protected]',NULL);
ERROR:  duplicate key value violates unique constraint "et_u"
insert into et (email,email2) values (NULL,'[email protected]');
ERROR:  duplicate key value violates unique constraint "et_u"
insert into et (email,email2) values (NULL,'[email protected]');
ERROR:  duplicate key value violates unique constraint "et_u"

Upvotes: 0

mu is too short
mu is too short

Reputation: 434985

I think you'll have to use a trigger to enforce your cross-column uniqueness constraint. If you add unique indexes on each column and then a trigger something like this (untested off the top of my head code):

CREATE FUNCTION no_dups_allowed() RETURNS trigger AS $$
DECLARE
    r ROW;
BEGIN
    SELECT 1 INTO r
    FROM users
    WHERE LOWER(email)     = LOWER(NEW.email_new)
       OR LOWER(email_new) = LOWER(NEW.email);
    IF FOUND THEN
        -- Found a duplicate so it is time for a hissy fit!
        RAISE 'Duplicate email address found' USING ERRCODE = 'unique_violation';
    END;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

You'd want something like that as a BEFORE INSERT and BEFORE UPDATE trigger. That trigger would take care of catching cross-column duplicates and the unique indexes would take care of in-column duplicates.

Some useful references:

You'll want the individual indexes for your queries anyway and using the uniqueness half of the indexes simplifies your trigger by leaving it to only deal with the cross-column part; if you try to do it all in the trigger, then you'll have to watch out for updating a row without really changing the email or email_new columns.

For the querying half, you could create a view that used a UNION to combine the two columns. You could also create a function to merge the user's email addresses into one list. Hard to say which would be best without know more details of these other queries but I suspect that fixing all the other queries to know about email and email_new would be the best approach; you'll have to update all the other queries to use the view or function anyway so why build a view or function at all?

Upvotes: 4

Related Questions