Postgres hashing user defined types

Question

Note: m4 is being used so "_" prefixed strings are expanded (m4 is a macro pre-processor similar to the c preprocessor).

My Type:

CREATE TYPE UrlPair AS (                                                      
    HostName        varchar( _LIMIT_HOSTNAME ),                               
    ScriptName      varchar( _LIMIT_SCRIPTNAME )                              
);

Used in

CREATE TABLE SymbolTable_UrlPair (
    Symbol          _BIG_SYMBOL_SERIAL_TYPE     PRIMARY KEY,
    UrlPair         UrlPair                     
);

With index

CREATE INDEX SymbolTable_UrlPair_UrlPair 
    ON SymbolTable_UrlPair USING hash (UrlPair);

Gives:

psql:script:32: ERROR:  data type urlpair has no default operator class 
for access method "hash"

HINT:  You must specify an operator class for the index or define a default 
operator class for the data type.

Question

Ideally I would like the engine to concatinate the strings and use that for a hash. However, I am not fussy. Could someone show me the syntax for declaring this "Operator Class" for the access method hash.

I would have expected some default hashing behaviour for user defined types. I would really preffer to keep the type -- i.e., I don't want to expand it, as I will probably define a few more elaborate UDT's.

Frank Heikens · Accepted Answer · 2010-03-06 16:50:50Z

1

Hashing and a hash index are two different things. Hash indexes don't support a multi column index, that might be the problem, your type UrlPair is multi value.

What's wrong with a Btree-index? What problem do have to solve?

answered Mar 6, 2010 at 16:50

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hassan Syed Over a year ago

Symbolizing URLs in a very large database. A Linear Hashed index is preferable to a B-tree for strings AFAIK postgresql.org/docs/7.3/static/sql-createindex.html. This page states that a 'func_name' can be specified which is used to return a value that can be indexed. I am usuming this will allow the UDT to return a single entity that can be hashed (in my case a concatination of the URL).

Hassan Syed Over a year ago

Hmm switching to B-tree worked. I guess I will stick to a B-tree as I will probably use a range based scan on hostname. However I would still be interested in the syntax for the access method.

Magnus Hagander Over a year ago

@hassan: you're not actually using PostgreSQL 7.3, I hope? If so, your first thing to do is to get a supported, non-ancient release. And note that in most cases, btree indexes perform as good as or better than hash in PostgreSQL today.

Hassan Syed Over a year ago

Thanks, no I am working with 8.4. It is good to hear that about B-Trees

Collectives™ on Stack Overflow

Postgres hashing user defined types

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related