Find all non-foreign key columns of base tables

Copy SQL

WITH fk AS (SELECT 
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS key_schema,
c.relname AS key_table, 
o.conkey AS key_col,
c.oid AS key_table_oid
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
WHERE o.contype='f'),
fk_unnest AS (SELECT key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
FROM fk, unnest(fk.key_col) with ordinality AS k(key_col_num, ordin)),
fk_with_names AS (SELECT key_schema, key_table, a_key.attname AS key_col
FROM fk_unnest k INNER JOIN pg_attribute a_key ON k.key_col_num = a_key.attnum AND k.key_table_oid = a_key.attrelid AND a_key.attisdropped = FALSE)
SELECT table_schema, table_name, column_name, CASE WHEN data_type ILIKE 'character%' AND character_maximum_length IS NOT NULL THEN data_type || '(' || character_maximum_length::text || ')'
WHEN data_type ILIKE 'timestamp%' AND datetime_precision IS NOT NULL THEN data_type || '(' || datetime_precision || ')'
WHEN data_type ILIKE 'numeric%' AND numeric_precision IS NOT NULL THEN data_type || '(' || numeric_precision::text || ',' ||coalesce(numeric_scale,0)::text || ')'
WHEN data_type ILIKE 'interval%' AND interval_type IS NOT NULL THEN data_type || '(' || interval_type::text || ')'
WHEN data_type='USER-DEFINED' THEN udt_schema || '.' || udt_name 
ELSE data_type END AS data_type
FROM INFORMATION_SCHEMA.columns AS c
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)
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
WHERE table_type='BASE TABLE')
AND NOT EXISTS (SELECT 1
FROM fk_with_names AS fwn
WHERE c.table_schema=fwn.key_schema
AND c.table_name=fwn.key_table
AND c.column_name=fwn.key_col)
ORDER BY table_schema, table_name, ordinal_position;