Search code examples
sqlpostgresqlindexingddl

Index length for varchar column in PostgreSQL

In MySQL we can set the length of the field that will be indexed this way:

CREATE INDEX part_of_name ON customer (name(10));

Can we do the same in PostgreSQL? If yes, then how?

Solution

  • If the data type allows modifying the length, the syntax is close:

    create index part_of_name on customer((customer_name::varchar(10)));

    Here is an example how this index can be used; you have to use the same type cast in the condition.

    and for the text I assume @Mureinik suggestion is the only option. Effectively the index with the cast is expression based index anyway, so my answer is kind of same, but with closer to the OP syntax.