Re: Unique rows without a key

On Thu, 14 Jun 2001, Steve Micallef wrote:

> > You could have one additional column that contains a hash of the entire row, so you'd only need a unique index on
thishash column. 
> >
>
> Thanks for your help. However I was thinking more along the lines of using
> the table itself as the index. Creating a hash column with all the other
> columns as part of it still significantly increases my table size.

It's not that much of an overhead especially if your table has many attributes. There is a function hashname() that
willreturn an integer after hashing the text it takes as argument. I didn't find any documentation, but seems to work. 
I tried the following and it was ok:

thalis=# CREATE TABLE xoxo (name char(10),id int4,hashval int4);
CREATE
thalis=# CREATE FUNCTION mytrig_func() RETURNS OPAQUE AS 'BEGIN NEW.hashval=hashname(NEW.name||NEW.id); RETURN NEW;
END;'LANGUAGE 'plpgsql'; 
CREATE
thalis=# CREATE TRIGGER xoxo_trig_ins BEFORE INSERT ON xoxo FOR EACH ROW EXECUTE PROCEDURE mytrig_func();
CREATE
lis=# insert into xoxo(name,id) values('thalis',13);
INSERT 9347654 1
thalis=# select * from xoxo;
    name    | id |  hashval
------------+----+-----------
 thalis     | 13 | 644124107
(1 row)

Then you just add a unique index on hashval and you are done :-)

cheers,
thalis


>
> Thanks,
>
> Steve.
>
>