Check as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming.
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. The query tries to give suggestions whether the column name should be in singular or plural form. For that it also considers the type of the column. For instance, array, xml, and json values can contain data about multiple entities or relationships and thus the name of the column that has the type should better be in plural. The names of colums with a numeric type are considered OK even if they are in plural because it is assumed that the value shows the number of entities. Thus, nr_of_docs with type INT is considered to be plural and OK whereas docs with type TEXT is considered to be plural and not OK.
Type
General (Overview of some aspect of the database.)
Prefer singular form. Plural form may indicate that there should be a separate table instead of the column.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH columns AS (SELECT nspname AS table_schema, relname AS table_name,
CASE WHEN relkind='r' THEN 'BASE TABLE'
WHEN relkind='v' THEN 'VIEW'
WHEN relkind='m' THEN 'MATERIALIZED VIEW'
WHEN relkind='f' THEN 'FOREIGN TABLE'
WHEN relkind='p' THEN 'PARTITIONED TABLE'
END AS table_type,
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 IN ('r','v','m','f','p')
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_or_singular AS (SELECT table_schema, table_name, table_type, 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$|needs)','','g') ~* '(?
Collections
This query belongs to the following collections:
Name
Description
Find problems about names
A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.
Lexicon bad smells and linguistic antipatterns
Queries made to find the occurrences of lexicon bad smells and linguistic antipatterns
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.
Further reading and related materials:
Reference
Linguistic antipatterns "D.1 Says one but contains many" and "E.1 Says many but contains one": Arnaoudova, V., Di Penta, M., Antoniol, G., 2016. Linguistic antipatterns: What they are and how developers perceive them. Empirical Software Engineering, 21(1), pp.104-158.