The list of all the queries

Sometimes extract, sometimes date_part

Query goal: Find as to whether you sometimes use date_part function and sometimes extract function. These implement the same functionality.
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: Prefer extract function because it has been specified in the SQL standard. Try to be consistent.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c'   
and n.nspname 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 c.table_schema, c.table_name || '.' || c.column_name, coalesce(c.column_default, domain_default) AS default_value,
CASE WHEN c.column_default IS NOT NULL THEN 'COLUMN DEFAULT' ELSE 'DOMAIN DEFAULT' END AS type
FROM information_schema.columns AS c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t  USING (table_schema, table_name)
WHERE c.table_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 (column_default IS NOT NULL OR domain_default IS NOT NULL)
union select n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' AS type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname 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 trigger_schema, event_object_table || '.' || trigger_name, action_condition, 'TRIGGER WHEN' AS type
FROM INFORMATION_SCHEMA.triggers
WHERE action_condition IS NOT NULL
UNION SELECT schemaname, tablename || '.' || rulename, definition, 'RULE WHERE' AS type
FROM pg_catalog.pg_rules
WHERE 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 table_schema, table_name, view_definition, type
FROM (SELECT  table_schema, table_name, regexp_replace(view_definition,'[\r\n]','<br>','g') AS view_definition, 'VIEW SUBQUERY' AS type
FROM information_schema.views
WHERE table_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 SELECT schemaname, matviewname, regexp_replace(definition,'[\r\n]','<br>','g') AS definition, 'MATERIALIZED VIEW SUBQUERY' AS type
FROM pg_catalog.pg_matviews) AS foo
UNION SELECT pg_namespace.nspname AS routine_schema,  pg_proc.proname || '(' || pg_get_function_identity_arguments(pg_proc.oid)  || ')' AS routine_name,  regexp_replace(pg_get_functiondef(pg_proc.oid),'[\r\n]','<br>','g')  AS routine_src, 'ROUTINE' AS type
FROM pg_catalog.pg_proc, pg_catalog.pg_namespace
WHERE pg_proc.pronamespace = pg_namespace.oid 
AND pg_proc.prokind<>'a'
AND pg_proc.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') 
AND pg_namespace.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND 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=pg_proc.oid))
SELECT schema, name AS object_identifier, type AS object_type, expression AS suspected_expression
FROM expressions
WHERE EXISTS (SELECT *
FROM expressions
WHERE expression~*'extract[[:space:]]*[(]')
AND EXISTS (SELECT *
FROM expressions
WHERE expression~*'date_part[[:space:]]*[(]')
AND expression~*'(date_part|extract)[[:space:]]*[(]'
ORDER BY schema, type, 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 .
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories where the query belongs to

Category nameCategory description
CHECK constraintsQueries of this category provide information about CHECK constraints.
Default valueQueries of this catergory provide information about the use of default values.
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
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

Reference materials for further reading

Reference
https://www.commandprompt.com/education/how-to-use-extract-function-in-postgresql/

The list of all the queries