Plese I would like to do in PostgreSQL something like
CREATE UNIQUE INDEX IF NOT EXISTS
Any idea?
PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
You can get the list of indexes, their table and column using this query: select t. relname as table_name, i. relname as index_name, a.
Right-click the table on which you want to create a unique index and select Design. On the Table Designer menu, select Indexes/Keys. In the Indexes/Keys dialog box, click Add. Select the new index in the Selected Primary/Unique Key or Index text box.
You can check, if an index with a given name exists with 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
Just another ready-to-use solution.
PostgreSQL v9.0+:
DO $BLOCK$
BEGIN
BEGIN
CREATE INDEX index_name ON table_name( column_name );
EXCEPTION
WHEN duplicate_table
THEN RAISE NOTICE 'index ''index_name '' on table_name already exists, skipping';
END;
END;
$BLOCK$;
PostgreSQL v9.5+:
CREATE INDEX IF NOT EXISTS index_name ON table_name( column_name );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With