The list of all the queries

Perhaps too many different prefixes in the names of database objects that have the same type

Query goal: One should be consistent in naming, including in the use of prefixes. If you use prefix in the name of a database object, then it should refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type. Find types of database objects in case of which there are different prefixes in different names.
Notes about the query: The query does not consider objects that are a part of an extension. The query does not consider the names of base tables and columns of tables.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Low (Many false-positive results)
Query license: MIT License
Fixing suggestion: Rename database objects to be more consistent.
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]*$','') 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)
),

prefixes AS (SELECT regexp_replace(object_name,'^(([[:alpha:]]|_)[[:alnum:]]{0,4})_.*','\1') AS prefix, object_type
FROM named_user_defined_objects
WHERE object_type NOT IN ('BASE TABLE','COLUMN')),

prefixes_all AS (SELECT CASE WHEN length(prefix)>4 THEN 'MISSING' ELSE prefix END AS prefix, object_type
FROM prefixes),

prefixes_statistics AS (SELECT prefix, object_type, Count(prefix) AS nr_of_occurrences
FROM prefixes_all
GROUP BY prefix, object_type)

SELECT object_type, string_agg(prefix || '(' || nr_of_occurrences::text || ')', ';' ORDER BY nr_of_occurrences DESC) AS prefixes, Count(prefix) FILTER (WHERE prefix<>'MISSING') AS nr_of_different_prefixes
FROM prefixes_statistics
GROUP BY object_type
HAVING Count(prefix)>1
ORDER BY Count(prefix) FILTER (WHERE prefix<>'MISSING') DESC, object_type;

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.

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
The corresponding code smells in case of cleaning code are "N1: Choose Descriptive Names" and "N6: Avoid Encodings". (Robert C. Martin, Clean Code)

The list of all the queries