Get unique_index key from a table

Questions / Help
1

Hi all

I created unique_index on the migration as follow:

def change do
  create unique_index(:countries_code, [:alpha2, :alpha3])
end

and want to know, where to get all fields, that should be unique.
I tried with following statement:

SELECT conname
FROM pg_constraint
WHERE conrelid = 'countries_code'::regclass::oid

but it shows me only countries_code_pkey but expect also :alpha2 and :alpha3.
mix ecto.migrate is already executed.

Thanks

2

@kostonstyle. You could try this.

SELECT i.relname as indname,
i.relowner as indowner,
idx.indrelid::regclass,
am.amname as indam,
idx.indkey,
ARRAY(
SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
FROM generate_subscripts(idx.indkey, 1) as k
ORDER BY k
) as indkey_names,
idx.indexprs IS NOT NULL as indexprs,
idx.indpred IS NOT NULL as indpred
FROM pg_index as idx
JOIN pg_class as i
ON i.oid = idx.indexrelid
JOIN pg_am as am
ON i.relam = am.oid
JOIN pg_namespace as ns
ON ns.oid = i.relnamespace
AND ns.nspname = ANY(current_schemas(false));

I try this after I connect to my database using$ psql databasename. and I get all indexes details.

Reference from : stackoverflow

1 Like
3

Thanks dude.

4

You are welcome.