The list of all the queries

Base table columns permitting negative prices/quantity

Query goal: Find non-foreign key base table columns that name refers to the possibility that these are used to register prices/quantities. Find the columns that do not have any simple CHECK constraints, i.e., a constraint that covers only this column. In this case registration of negative price/quantity is most probably not prohibited.
Notes about the query: The query takes into account only constraints that are associated with exactly one column. Thus, there could be constraints that apply restrictions to multiple columns that prevent negative values. The query takes into account constraints that are associated directly with the column as well as constraints that are associated with the column through a domain. The query considers both column names in English and Estonian.
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: If negative values are not permitted, then declare a CHECK constraint to the column. Investigate, as to whether price/quantity 0 is permitted or not.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH domains AS (
SELECT cdu.table_schema, cdu.table_name, cdu.column_name, (SELECT data_type FROM INFORMATION_SCHEMA.domains AS d
WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS data_type
FROM ((INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN
INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc
USING (domain_schema, domain_name)) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' 
AND cc.check_clause NOT LIKE '%IS NOT NULL'  
AND cdu.column_name~*'(price|hind|maksumus|_arv|kogus|hulk|quantity|qty)' 
AND cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND catalog_name IS NOT NULL 
AND schema_name IS NOT NULL)),

simple_chk AS (SELECT 
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name, 
a.attname AS column_name,
t.typname AS type_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1 
AND o.contype = 'c' 
AND a.attname~*'(price|hind|maksumus|_arv|kogus|hulk|quantity|qty)'
UNION SELECT table_schema, table_name, column_name, data_type
FROM domains),

suspected_columns AS (SELECT table_schema, table_name, column_name, data_type
FROM INFORMATION_SCHEMA.columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') 
AND 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_name~* '(price|hind|maksumus|_arv|kogus|hulk|quantity|qty)'
AND column_name!~* '(kood|code|_id)$'
AND column_name!~* '^(kood|code|id)_'
AND data_type IN ('smallint','integer','bigint','numeric')),

fk_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name
from (select 
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f') t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false)

SELECT table_schema, table_name, column_name, data_type
FROM suspected_columns AS pc
WHERE NOT EXISTS (SELECT 1 FROM simple_chk AS psc WHERE pc.table_schema=psc.table_schema AND pc.table_name=psc.table_name
AND pc.column_name=psc.column_name) 
AND NOT EXISTS (SELECT 1 FROM fk_columns AS f WHERE pc.table_schema=f.table_schema AND pc.table_name=f.table_name
AND pc.column_name=f.column_name)
ORDER BY table_schema, table_name, column_name;

Collections where the query belongs to

Collection nameCollection description
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. 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
CHECK constraintsQueries of this category provide information about CHECK constraints.
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).

Reference materials for further reading

Reference
https://www.reddit.com/r/todayilearned/comments/27exxb/til_when_amazoncom_launched_you_could_order_a/

The list of all the queries