Skip to content

Instantly share code, notes, and snippets.

@adis-io
Last active February 28, 2020 15:54
Show Gist options
  • Save adis-io/1b6f54d0aeeec75447903ebeda01f334 to your computer and use it in GitHub Desktop.
Save adis-io/1b6f54d0aeeec75447903ebeda01f334 to your computer and use it in GitHub Desktop.
Find tables in postgres with deleted_at column and unique indexes without condition #postgres #query #sql #safe_deletion

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'
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment