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. Show the number of tables that name is in plural or in singular by table type. |
Notes about the query: | 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 undersore and a single letter or a letter "s", then it is considered plural. Thus, "process" and "task_fs" are considered singular whereas "processes" and "clear_facts" are considered plural. |
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 plural_singular 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, CASE WHEN regexp_replace(relname,'(stats_|stats$|status$|state$|alias|address|pays$|group$|lock$|sys|class$)','','g') ~* '(?<!((^|_)[[:alnum:]]{0,2}|(^|_)[bcdfghjklmnpqrsztvwxz]{0,4}|s))s(_|$)' THEN 'Perhaps plural' ELSE 'Perhaps singular' END AS comment_about_the_table_name FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid WHERE 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)) SELECT table_type, comment_about_the_table_name, Count(*) AS nr_of_tables FROM plural_singular GROUP BY table_type, comment_about_the_table_name ORDER BY table_type, Count(*) DESC, comment_about_the_table_name; |
Collection name | Collection 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. |
Category name | Category 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. |