The list of all the queries

Names of database objects that perhaps end with a sequence number

Query goal: Find the names (identifiers) of user-defined database objects that end with one or more digit. Names should be informative. Duplicates should be avoided. Digits in names are a possible sign of duplication of database objects or unclear names.
Notes about the query: The query does not consider objects that are a part of an extension. In the result of the query (b) tags are used to highlight the digits. The query excludes the names where the number in the end refers to hashing algorithms md4, md5, sha1, sha2, or sha3. In case of database objects that are not CHECK constraints and domains the additional requirement to problematic names is that the name should not have any digits before the trailing digits. Thus, for instance, a CHECK constraint name "chk_item_weight_between_10_100" is considered to be non-problematic whereas base table name "Items_2022_1" is considered to be problematic. Names that have certain words like "than" or "least" before the trailing digits are not considered to be problematic. Names that end with four or more consecutive digits are not considered to be problematic. The query considers both names in Estonian and in English.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Do not use sequence numbers in names.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH named_objects AS (

SELECT 
 n.oid,
 ''::text AS container_name, 
'DATABASE' AS container_type,
n.nspname AS object_name,
'SCHEMA' AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')

UNION SELECT 
c.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
c.relname AS object_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='i' THEN 'INDEX'
WHEN c.relkind='S' THEN 'SEQUENCE GENERATOR'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='c' THEN 'COMPOSITE TYPE'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
WHEN c.relkind='I' THEN 'PARTITIONED INDEX'
END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind NOT IN ('t')

UNION ALL SELECT 
pt.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
pt.typname AS object_name,
CASE WHEN pt.typtype='d' THEN 'DOMAIN'
WHEN pt.typtype='e' THEN 'ENUMERATION TYPE'
WHEN pt.typtype='r' THEN 'RANGE TYPE' END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND pt.typtype NOT IN ('b','c')

UNION SELECT
o.oid,
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
conname AS object_name,
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 object_type
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')

UNION SELECT 
NULL AS oid,
domain_schema || '.' || domain_name  AS container_name,
'DOMAIN' AS container_type,
constraint_name AS object_name, 
'DOMAIN CHECK' AS object_type
FROM INFORMATION_SCHEMA.domain_constraints
WHERE domain_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)

UNION ALL SELECT 
pc.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
pc.proname AS object_name,
CASE WHEN pc.prokind='f' THEN 'FUNCTION'
WHEN pc.prokind='p' THEN 'PROCEDURE'
WHEN pc.prokind='a' THEN 'AGGREGATE FUNCTION'
WHEN pc.prokind='w' THEN 'WINDOW FUNCTION' END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace
WHERE (nspname='public' OR rolname<>'postgres')

UNION SELECT 
translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid,
specific_schema || '.' || regexp_replace(specific_name,'_[0-9]*$','') || '(' || pg_get_function_identity_arguments(translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid) || ')' AS container_name,  
'ROUTINE' AS container_type,
parameter_name AS object_name, 
 'PARAMETER' AS object_type
FROM information_schema.parameters
WHERE specific_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 parameter_name IS NOT NULL

UNION SELECT 
tr.oid,
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
tr.tgname AS object_name,
'TRIGGER' AS object_type
FROM pg_trigger tr INNER JOIN pg_class c ON tr.tgrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND tr.tgisinternal='f'
AND tr.tgconstraint=0

UNION SELECT 
NULL AS oid,
schemaname || '.' || tablename AS container_name,
'TABLE' AS container_type,
rulename AS object_name,
'RULE' AS object_type
FROM pg_rules AS r
WHERE r.schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)

UNION SELECT 
NULL AS oid,
'' AS container_name,
'DATABASE' AS container_type,
evtname AS object_name,
'EVENT TRIGGER' AS object_type
FROM pg_event_trigger 

UNION ALL SELECT 
c.oid,
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
at.attname AS object_name,
'COLUMN' AS object_type
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','v','m','f','p')
AND at.attisdropped='f'
AND at.attnum>0

),
named_user_defined_objects AS (
SELECT container_name, container_type, object_name, object_type
FROM named_objects nob
WHERE NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=nob.oid)
)

SELECT container_name, container_type, regexp_replace(object_name, '([[:digit:]])', E'<b>\\1</b>', 'g') AS suspected_name, 
object_name AS suspected_name_original,
object_type AS suspected_object_type
FROM named_user_defined_objects
WHERE ((object_type ~*'(CHECK|DOMAIN)' AND object_name~'^[^[:digit:]]+[[:digit:]]+$')
OR (object_type !~*'(CHECK|DOMAIN)' AND object_name~'[[:digit:]]+$'))
AND object_name!~*'(kui|kuni|v(a|ä)hemalt|minimaalselt|maksimaalselt|than|until|least|min|max)_[[:digit:]]+$'
AND object_name!~*'(md(4|5)|sha(1|2|3))$'
AND object_name!~*'([[:digit:]])\1\1\1+$'
ORDER BY object_type, container_name, object_name;

Collections where the query belongs to

Collection nameCollection description
Find problems about namesA 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.
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories where the query belongs to

Category nameCategory description
Duplication of implementation elementsQueries of this catergory provide information about the duplication of the database objects.
NamingQueries of this category provide information about the style of naming.

Reference materials for further reading

Reference
The use of digits in names is a sign of the following antipatterns from the Bill Karwin's book of SQL antipatterns: Multicolumn Attributes (Chapter 8), Metadata Tribbles (Chapter 9).

The list of all the queries