IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'MyTableIndex' AND object_id = OBJECT_ID('tablename'))
    BEGIN
        -- Index with this name, on this table does NOT exist
    END

Upon closer inspection, it seems like there were too instances of the application attempting to migrate the same table. Presumably, the IF NOT EXISTS condition was checked at a moment when that was a true statement, but by the time it executed the CREATE statement, there was already a conflicting index.

In SQL Server 2016, this is very easy, you just need to make a choice between having simple scripts or enjoying whatever performance you've actually observed from DROP_EXISTING (is this quantifiable? Have you tested it?).

CREATE TABLE dbo.what(i int, INDEX x(i));
GO

DROP INDEX IF EXISTS dbo.what.x;
GO

CREATE INDEX x ON dbo.what(i DESC);
GO

Take a look at the System tables:

https://devzone.advantagedatabase.com/dz/webhelp/Advantage10/devguide_system_tables.htm

In particular there is a table called system.indexes:

https://devzone.advantagedatabase.com/dz/webhelp/Advantage10/master_system_indexes.htm

Index names in PostgreSQL

• Index names are unique across a single database schema.
• Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema.
• Two tables in the same schema cannot have an index of the same name. (Follows logically.)

If you do not care about the name of the index, have Postgres auto-name it:

CREATE INDEX ON tbl1 (col1);

is (almost) the same as:

CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

Except that Postgres will avoid a naming collisions and automatically pick the next free name:

tbl1_col1_idx 
tbl1_col1_idx2
tbl1_col1_idx3
...

Just try it. But, obviously, you would not want to create multiple redundant indexes. So it wouldn't be a good idea to just blindly create a new one.

Test for existence

Postgres 9.5 or newer

Now available:

CREATE INDEX IF NOT EXISTS ...

Also works for CREATE INDEX CONCURRENTLY IF NOT EXISTS.

However, the manual warns:

Note that there is no guarantee that the existing index is anything like the one that would have been created.

It's a plain check for the object name. (Applies to variants for older versions below, too.)
To find existing indexes on the same table for the same column(s):

SELECT pg_get_indexdef(indexrelid)
FROM   pg_index
WHERE  indrelid = 'public.big'::regclass
AND   (indkey::int2[])[:] = ARRAY (
   SELECT attnum
   FROM   unnest('{usr_id, created_at}'::text[]) WITH ORDINALITY i(attname, ord)
   JOIN  (
      SELECT attname, attnum
      FROM   pg_attribute
      WHERE  attrelid = 'public.big'::regclass
      ) a USING (attname)
   ORDER BY ord
   );

Restrictions:

• Only works for columns, not other index expressions.
• Also reports partial indexes (with WHERE clause) and covering indexes (with INCLUDE clause).
• Reports any type of index, not just B-tree indexes.

Study the results (if any) before proceeding, or refine the query to your needs ...

Further reading:

• Find tables with multiple indexes on same column
• Normalize array subscripts for 1-dimensional array so they start with 1

Postgres 9.4

You can use the new function to_regclass() to check without throwing an exception:

DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

Returns NULL if an index (or another object) of that name does not exist. See:

• How to check if a table exists in a given schema

This doesn't work for CREATE INDEX CONCURRENTLY, since that variant cannot be wrapped in an outer transaction. See comment by @Gregory below.

Postgres 9.3 or older

Cast the schema-qualified name to regclass:

SELECT 'myschema.myname'::regclass;

If it throws an exception, the name is free.
Or, to test the same without throwing an exception, use a DO statement:

DO
$$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relname = 'mytable_mycolumn_idx'
      AND    n.nspname = 'myschema'
   ) THEN
    
        CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
    END IF;
END
$$;

The DO statement was introduced with Postgres 9.0. In earlier versions you have to create a function to do the same.
Details about pg_class in the manual.
Basics about indexes in the manual.