0

First and foremost I am using postgres version 9.4 . I am trying to create a partial index on this query

select DISTINCT ON(city,state)city,state,zip from zips where city ilike 
'%' and state ilike '%' limit 10

My issue is that I do not know how to put columns inside the quotes '%' so that the partial index looks like this

 select DISTINCT ON(city,state)city,state,zip from zips where city ilike 
    '{city}%' and state ilike '{state}%' limit 10

What is the correct method to put columns inside a single quote mark ? I have been looking all over the place. Right now my partial index looks like this

CREATE INDEX "Location_Search"
   ON zips (city ASC NULLS LAST, state ASC NULLS LAST)
   where city ilike 'city%' and state ilike 'state%';

Which is obviously not helping because postgres is treating the 'city%' and state ilike 'state%' as variables and not columns. I have a search-box where users type in the city and state fields so I want to optimize. Any help would be appreciated ...

user1949387
  • 1,245
  • 3
  • 21
  • 38
  • I don't understand why you think you need a partial index. Just create one index on each column `create index on zips (city)` and `create index on zips (state)`. But a condition `state ilike '%'` will **never** use an index because it select all rows anyway –  Feb 06 '16 at 09:31
  • And even if you define such an index, it is pretty useless, as the condition `city LIKE city||'%'` is **always** true for non-null value. It is an expensive way of writing `city is not null` –  Feb 06 '16 at 09:40

1 Answers1

0

try this

 WHERE column_name LIKE column_name||'%'

see this Link

Community
  • 1
  • 1
Shubham Batra
  • 2,357
  • 5
  • 29
  • 48