Find foreign key constraints that cover one column in case of which the name of refererenced/referencing column is in plural and the name of referencing/refererenced column is in singular.
Notes
The query works somewhat correctly only if the names are in English. The query is based on a simplified assumption that in case of many words in English (of course, not all) the plural form is indicated by the letter "s" at the end of the word. If the name or its subcomponent ends with s and it is not preceded with an underscore and a single letter or a letter "s", then it is considered plural.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH columns AS (SELECT nspname AS table_schema, relname AS table_name,
attname AS column_name,
CASE WHEN typbasetype=0 THEN pg_type.typname
ELSE (SELECT typname
FROM pg_type AS domain_type
WHERE domain_type.oid=pg_type.typbasetype) END AS column_type,
CASE WHEN attndims<>0 OR typndims<>0 THEN TRUE ELSE FALSE END AS is_array
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
INNER JOIN pg_type ON pg_attribute.atttypid =pg_type.oid
WHERE attnum>=1
AND relkind ='r'
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
plural_singular AS (SELECT table_schema, table_name, column_name,
CASE WHEN is_array=FALSE THEN column_type ELSE 'ARRAY [' || translate(column_type,'_','') || ']' END AS column_type,
CASE WHEN regexp_replace(column_name,'(stats_|stats$|status|state|is_|has_|pos$|alias|cvs|address|value$|key$|pays$|group$|lock$|_to$|_id$|_code$|_type$|_name$|posts_to|sales_tax|trans_type|_date$|_time$|_flag$|number|sys|class$|basis$)','','g') ~* '(?pst.comment_about_the_column_name
order by target_schema, target_table, conname;
Categories
This query is classified under the following categories:
Name
Description
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Naming
Queries of this category provide information about the style of naming.