Dowemo
0 0 0 0


Question:

Is there any way to create a primary key using the hash method? Neither of the following statements work:

oid char(30) primary key using hash
primary key(oid) using hash

Best Answer:


I assume, you meant to use the hash index method / type.

Primary keys are constraints. Some constraints can create index(es) in order to work properly (but this fact should not be relied upon). F.ex. a UNIQUE constraint will create a unique index. Note, that only B-tree currently supports unique indexes. The PRIMARY KEY constraint is a combination of the UNIQUE and the NOT NULL constraints, so (currently) it only supports B-tree.

You can set up a hash index too, if you want (besides the PRIMARY KEY constraint) -- but you cannot make that unique.

CREATE INDEX name ON table USING hash (column);

But, if you are willing to do this, you should be aware that there is some limitation on the hash indexes:

Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged.

Also:

Currently, only the B-tree, GiST and GIN index methods support multicolumn indexes.

Note: unfortunately, oid is not the best name for a column in PostgreSQL, because it can also be a name for a system column and type.

Note 2: the char(n) type also discuraged. You can use varchar or text instead, with a CHECK constraint -- or (if the id is so uuid-like) the uuid type itself.




Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs