The list of all the queries

Database objects of the same type and case insensitive name in the same container

Query goal: Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client
Notes about the query: In case of routines the query takes into account the possibility of overloading and looks only routines with the same case insensitive name and identity parameters.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: High (Few or no false-positive results)
Query license: MIT License
Fixing suggestion: Rename objects so that the name is not case sensitive, i.e., use regular identifier instead of delimited identifier as the column name. Remove duplicate objects if needed.
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='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','i')

UNION SELECT 
c.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
c.relname AS object_name,
'INDEX' 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
INNER JOIN pg_index AS i ON i.indexrelid=c.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND i.indisprimary = FALSE 
AND i.indisexclusion =FALSE

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
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
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)
AND cc.check_clause NOT LIKE '%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)
)

SELECT container_name, container_type, lower(object_name) AS object_name, object_type, Count(*) AS number_of_ocurrences, 
string_agg(object_name, ';<br>' ORDER BY object_name) AS objects
FROM named_user_defined_objects
GROUP BY container_name, container_type, lower(object_name), object_type
HAVING Count(*)>1
ORDER BY object_type, container_name, lower(object_name);

Collections where the query belongs to

Collection nameCollection description
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
Case sensitivity vs. case insensitivityQueries of this category provide information about case sensitivity/insensitivity of identifiers or user data.
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
DomainsQueries of this category provide information about reusable specifications of column properties.
NamingQueries of this category provide information about the style of naming.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.
User-defined routinesQueries of this category provide information about the user-defined routines

The list of all the queries