Query goal: | For different types of constraints find the number of columns covered with constraints of such type. |
Notes about the query: | PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog. |
Query type: | Sofware measure (Numeric values (software measures) about the database) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH constr AS (SELECT nc.nspname AS constr_schema, o.conrelid AS constr_table, unnest(o.conkey) AS constr_col, CASE WHEN o.contype='p' THEN 'PRIMARY KEY' WHEN o.contype='u' THEN 'UNIQUE' WHEN o.contype='f' THEN 'FOREIGN KEY' WHEN o.contype='c' THEN 'TABLE CHECK' WHEN o.contype='x' THEN 'EXCLUDE' WHEN o.contype='t' THEN 'CONSTRAINT TRIGGER' END AS constr_type FROM pg_constraint o INNER JOIN pg_class c on c.oid = o.conrelid inner join pg_namespace as nc on nc.oid=c.relnamespace inner join pg_authid AS a on nc.nspowner=a.oid where (nc.nspname='public' or rolname<>'postgres')), nr_of_all_cols AS (SELECT Count(*)::numeric AS nr_of_all_cols FROM information_schema.columns AS c INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name) WHERE c.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND table_type='BASE TABLE') SELECT constr_schema, constr_type, Count(DISTINCT constr_table::text || '.'|| constr_col::text) AS nr_of_columns, Round(Count(DISTINCT constr_table::text || '.'|| constr_col::text)::numeric*100/(SELECT nr_of_all_cols FROM nr_of_all_cols),1) AS percentage_of_columns FROM constr GROUP BY CUBE (constr_schema, constr_type); |
Collection name | Collection description |
---|---|
Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview |
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Category name | Category description |
---|---|
CHECK constraints | Queries of this category provide information about CHECK constraints. |
Relationships between tables | Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |