Query below summarizes default constraints in a database listing all distinct default value definitions for columns with the number of their occurrence.
select column_default, count(distinct table_schema || '.' || table_name) as tables, count(column_name) as columns from information_schema.columns where table_schema not in ('pg_catalog', 'information_schema') group by column_default order by tables desc, columns desc;
- column_default - definition of default constraint (formula) and NULL for columns without a default value
- tables - number of tables with this constraint (or number of tables with columns with no constraints for NULL row)
- columns - number of columns with this particular constraint (or no constraint for NULL row)
- One row represents one unique definition of default value
- Scope of rows: all distinct definitions of default column values in a database and one NULL row for columns without constraints
- Ordered by the number of occurrences from the most popular