Find tables in postgres with deleted_at column and unique indexes without condition #postgres #query #sql #safe_deletion

README.md

So in Postgres you can create partial indexes with condition.
Let's say you have soft deletion in you table and you don't want them to be indexed.

You added deleted_at column, but forget change unique indexes. To find those tables manually will be a bit longer.

Here is SQL query which will help you find them faster:

SELECT tables.table_name
FROM information_schema.tables as tables
INNER JOIN (
  SELECT table_name
  FROM information_schema.columns
  WHERE column_name = 'deleted_at'
) tables_with_deleted_at ON tables_with_deleted_at.table_name = tables.table_name
INNER JOIN (
  SELECT t.relname as table_name
  FROM pg_index i
  JOIN pg_class t ON t.oid = i.indrelid
  JOIN pg_class ix ON ix.oid = i.indexrelid
  WHERE 1=1
  AND indisunique AND NOT position('pkey' in ix.relname) > 0
  AND NOT (
  position(' WHERE ' in pg_get_indexdef(indexrelid)) > 0 AND position('deleted_at' in pg_get_indexdef(indexrelid)) > 0
  )
  GROUP BY t.relname
) tables_with_unique_index ON tables_with_unique_index.table_name = tables.table_name
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
;