Monday, March 28, 2011

PostgreSQL and (Index) Names

Creating an index in PostgreSQL can be achieved by executing the following SQL command:
ON "<tableName>" 
USING btree (<columnNames>);

There is one caveat, though. If you specify an index name that is longer than 63 characters PostgreSQL will truncate this and not tell you. By issuing the following command you can list all indexes present in the database:
SELECT * FROM pg_catalog.pg_indexes;

In general a limit of 63 characters should not be a problem. However, in my case I was working on a tool which generates index names and it hit this limit. Of course, now I’m using a different algorithm to generate the index names and the problem is solved.
I also suspect that this 63 character limit applies to other names as well. PostgreSQL has defined a type ‘name’ which has a size of 64 (including the terminating character). This type is used in several places and its definition info is available via the statement
FROM pg_catalog.pg_type
WHERE typname='name';

Personally I would prefer if PostgreSQL would reject the CREATE INDEX statement and instead return an error message.
Note: The above SQL statements may use PostgreSQL specific syntax, tables, views and functions. Other database systems may require a different syntax and may have different limitations on names. For my experiments I used PostgreSQL 9.0.2 (64bit) running on Windows 7.


Post a Comment

All comments, questions and other feedback is much appreciated. Thank you!