The list of all the queries

Check as to wheteher the names of columns are in the plural or in the singular form (Estonian version)

Query goal: Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.
Notes about the query: The query works somewhat correctly only if the names are in Estoniand. The query is based on a simplified assumption that in case of many words in Estonian (of course, not all) the plural form is indicated by the letter "d" at the end of the word. If the name or its subcomponent ends with de_ or te_, then it is considered plural. Names that for example end with id, oid, kood, or uuid are considered singular. Thus, "auto" is considered singular whereas "autod" and "autode_aruanne" are 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, kaubad with type INT is considered to be plural and OK whereas kommentaarid with type TEXT is considered to be plural and not OK.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Fixing suggestion: 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: Click on query to copy it

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 column_name ~* '(d$|.+(d|[^t]t)e_.+)' 
AND column_name !~*'((^|_)id$|kood$|(^|_)oid$|(^|_)uuid$|hind$|seisund$|brand$|^d*d$|periood$|arv$|laud$|kraad$|l(y|ü)hend$)' 
THEN 'Perhaps plural' ELSE 'Perhaps singular' END AS comment_about_the_column_name
FROM columns)
SELECT table_schema, table_name, table_type, column_name, column_type, comment_about_the_column_name,
CASE WHEN comment_about_the_column_name='Perhaps plural' AND column_type!~*'(array|json|xml)' 
THEN 'Perhaps should be singular'
WHEN comment_about_the_column_name='Perhaps singular' AND column_type~*'(array|json|xml)' THEN 'Perhaps should be plural'
ELSE 'OK' END AS evaluation
FROM plural_or_singular
ORDER BY evaluation DESC, comment_about_the_column_name, table_type, table_schema, table_name, column_name;

Categories where the query belongs to

Category nameCategory description
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
NamingQueries of this category provide information about the style of naming.

Reference materials for further reading

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.

The list of all the queries