I wanted the equivalent of sqlite's "create index if not exists" on
postgresql. Here's a solution for web2py. It is useful whenever you
set up a new database, or migrate new tables to an existing database
after a code update and want to ensure the right indexes are set up.
def create_indices_on_postgres():
'''Creates a set of indices if they do not exist'''
## Edit this list of table columns to index
## The format is [('table', 'column')...]
indices = [('actions', 'study'),
('actions', 'assid'),
('actions', 'hitid'),
('actions', 'time'),
('actions', 'workerid'),
('countries', 'code'),
('continents', 'code'),
('ips', 'from_ip'),
('ips', 'to_ip')]
for table, column in indices:
index_exists = db.executesql("select count(*) from pg_class
where relname='%s_%s_idx';"
% (table, column))[0][0] == 1
if not index_exists:
db.executesql('create index %s_%s_idx on %s (%s);'
% (table, column, table, column))
db.commit()
