CODEWITHSUNDEEP
sqlpostgresqlindexingddl
user5182503
user5182503

Reputation:

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?

Upvotes: 1

Views: 2229

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51609

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.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311988

I'm not aware of a way to do this directly, but Postgres supports indexes on expressions, so you can get the same behavior by indexing a substring call:

CREATE INDEX part_of_name ON customer (SUBSTRING(customer_name FOR 10));

Upvotes: 3

Related Questions