CREATE UNIQUE INDEX IF NOT EXISTS in postgreSQL

sql postgresql ddl
user3429578 picture user3429578 · Jul 10, 2014 · Viewed 24.6k times · Source

Plese I would like to do in PostgreSQL something like

CREATE UNIQUE INDEX IF NOT EXISTS

Any idea?

Answer

Torge picture Torge · Jan 11, 2016

You can check, if an index with a given name does exist by this statement.

If your index name is some_table_some_field_idx

SELECT count(*) > 0
FROM pg_class c
WHERE c.relname = 'some_table_some_field_idx' 
AND c.relkind = 'i';

Starting from Postgres 9.5 you can even use

CREATE INDEX IF NOT EXISTS

Related questions

Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More
PostgreSQL create table if not exists

In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL?

Read More
Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

I want to create a database which does not exist through JDBC. Unlike MySQL, PostgreSQL does not support create if not exists syntax. What is the best way to accomplish this? The application does not know if the database exists …

Read More