As we known, there're prefix length column which can be added during index creation. For example in mysql,
alter table j1 add index idx_j1_str1 (str1(5));
I didn't find any equivalent solution in postgresql after I searched the google.com and stackoverflow.com.
Then can anyone tell me the answer except the function index in postgresql.
Any reply will be appreciated.
create index idx_j1_str on j1 (left(str1,5));
But I don't think you need something like this in Postgres. An index on just str1 is probably much more versatile. But of course this depends heavily on the queries you run - which you did not show us, so it's impossible to say what kind of index you really need.
To make use of a function based index in Postgres (and basically any other DBMS that supports them) your query needs to contain the same expression as you used in the index:
select *
from j1
where left(str1,5) = '1234'
will use the above index (if it makes sense, e.g. if the table is large enough and the condition reduces the overall result substantially).
If you create a regular index on that column:
create index idx_j1_str on j1 (str1 varchar_pattern_ops);
then it can be used for something like:
select *
from j1
where str1 like '1234%'
(which is equivalent to left(str1,5) = '1234') but it can also be used for:
select *
from j1
where str1 like '1234678%'
or any other prefix search using a wildcard.
