Tim Mattison

Hardcore tech

Checking PostgreSQL to See if an Index Already Exists

| Comments

In my last post I showed you a simple way to check to see if a constraint already existed in PostgreSQL. Now I want to show you how to do the same thing for an index.

Here’s the code but keep in mind that it makes the assumption that everything is in the public schema.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE OR REPLACE FUNCTION create_index_if_not_exists (t_name text, i_name text, index_sql text) RETURNS void AS $$
DECLARE
  full_index_name varchar;
  schema_name varchar;
BEGIN

full_index_name = t_name || '_' || i_name;
schema_name = 'public';

IF NOT EXISTS (
    SELECT 1
    FROM   pg_class c
    JOIN   pg_namespace n ON n.oid = c.relnamespace
    WHERE  c.relname = full_index_name
    AND    n.nspname = schema_name
    ) THEN

    execute 'CREATE INDEX ' || full_index_name || ' ON ' || schema_name || '.' || t_name || ' ' || index_sql;
END IF;
END
$$
LANGUAGE plpgsql VOLATILE;

You can now use the function like this:

1

SELECT create_index_if_not_exists('table', 'index_name', '(column)');

No duplicated data, no exceptions. Enjoy!

Comments