Re: Best way to use indexes for partial match at beginning - Mailing list pgsql-general

On 2005-11-09 13:08, Martijn van Oosterhout wrote:
>> I want to run fast queries by knowing first characters of bar like :
>>
>> 1. Select records from foo where first character of bar is A
>> 2. Select records from foo where first character of bar is B
>> 3. Select records from foo where first  two characters of bar are BC
>> 4. Select records from foo where first  three characters of bar are ABC
>>
>
> SELECT * FROM foo WHERE bar LIKE 'A%';
> SELECT * FROM foo WHERE bar LIKE 'B%';
> SELECT * FROM foo WHERE bar LIKE 'BC%';
> SELECT * FROM foo WHERE bar LIKE 'ABC%';
>
> Have a nice day,
>

Or:

SELECT * FROM foo WHERE bar::CHAR(1) = 'A';
SELECT * FROM foo WHERE bar::CHAR(1) = 'B';
SELECT * FROM foo WHERE bar::CHAR(2) = 'BC';
SELECT * FROM foo WHERE bar::CHAR(3) = 'ABC';

-- Dean