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 this hash 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 will return 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.
>
>