I have an index on upper(general.cat_url(category_id)) on a table. when i add a column *with* default value , a query that previously used to give result does not give results anymore. REINDEX'ing the table produces correct result. if no default value is giving while adding the column the query continues to give proper result.
Regds mallah.
tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE'; +-------------+ | category_id | +-------------+ | 1 | +-------------+ (1 row)
tradein_clients=> explain SELECT category_id from general.web_category_master where upper(general.cat_url(category_id)::text) = 'AGRICULTURE'; +-------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------+ | Index Scan using web_category_master_upper_cat_url on web_category_master (cost=0.00..8.02 rows=1 width=4) | | Index Cond: (upper((general.cat_url(category_id))::text) = 'AGRICULTURE'::text) | +-------------------------------------------------------------------------------------------------------------+ (2 rows)
tradein_clients=> ALTER TABLE general.web_category_master add test_id int default 0; ALTER TABLE tradein_clients=> SELECT category_id from general.web_category_master where upper(general.cat_url (category_id)::text) = 'AGRICULTURE'; +-------------+ | category_id | +-------------+ +-------------+ (0 rows)
