Extreme contraction

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, object_name AS suspected_name, object_type
FROM named_user_defined_objects
WHERE object_name~'^([[:alnum:]]{1,3}_){3,}'
OR object_name~'([[:alnum:]]{1,3}_){4,}'
OR object_name~*'([bcdfghjklmnpqrstvwxyz]{1,3}_){2,}'
OR object_name~*'_[bcdfghjklmnpqrstvwxyz]{1,3}_.*_[bcdfghjklmnpqrstvwxyz]{1,3}_'
ORDER BY object_type, container_name, object_name;

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'

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, object_name AS suspected_name, object_type
FROM named_user_defined_objects
WHERE object_name~'^([[:alnum:]]{1,3}(_|$)){3,}'
OR object_name~'(((^|_)[[:alnum:]]){1,3}(_|$)){4,}'
OR object_name~*'(^|_)[bcdfghjklmnpqrstvwxyz]{1,3}_.*_[bcdfghjklmnpqrstvwxyz]{1,3}(_|$)'
ORDER BY object_type, container_name, object_name;

Query goal:	Find names that contain extremely short terms, due to an excessive word contraction, abbreviation, or acronym usage.
Notes about the query:	The query assumes that the name components (terms) are separated by "_", i.e., the snake case style is used. Short term is one to three characters long. The query finds identifiers that start with three or more consecutive short terms, contain four or more consecutive short terms, or contain at least two short terms that consist of only consonants.
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
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, object_name AS suspected_name, object_type FROM named_user_defined_objects WHERE object_name~'^([[:alnum:]]{1,3}_){3,}' OR object_name~'([[:alnum:]]{1,3}_){4,}' OR object_name~'([bcdfghjklmnpqrstvwxyz]{1,3}_){2,}' OR object_name~'_[bcdfghjklmnpqrstvwxyz]{1,3}_._[bcdfghjklmnpqrstvwxyz]{1,3}_' ORDER BY object_type, container_name, object_name; 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' 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, object_name AS suspected_name, object_type FROM named_user_defined_objects WHERE object_name~'^([[:alnum:]]{1,3}(_\|$)){3,}' OR object_name~'(((^\|_)[[:alnum:]]){1,3}(_\|$)){4,}' OR object_name~'(^\|_)[bcdfghjklmnpqrstvwxyz]{1,3}_.*_[bcdfghjklmnpqrstvwxyz]{1,3}(_\|$)' ORDER BY object_type, container_name, object_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.
Lexicon bad smells and linguistic antipatterns	Queries made to find the occurrences of lexicon bad smells and linguistic antipatterns

Extreme contraction

Collections where the query belongs to

Categories where the query belongs to

Reference materials for further reading

Reference
https://www.sqlstyle.guide/
Smell "Extreme contraction": Abebe, S.L., Haiduc, S., Tonella, P., Marcus, A., 2011. The effect of lexicon bad smells on concept location in source code. In 2011 IEEE 11th International Working Conference on Source Code Analysis and Manipulation (pp. 125-134). IEEE.