Listing all relations with schema and columns in PostgreSQL

Question

I am trying to list all the relations with their corresponding schema and columns in PostgreSQL using System Catalog constructs. But it also ends up showing some index names along with relation names, and shows attributes like cmax, cmin, ctid, etc. and so on which are not the actual attributes that I had create while constructing the table. Here is my query:

SELECT
  ns.nspname               AS schema_name,
  idx.attrelid :: REGCLASS AS table_name,
  idx.attname              AS column_name     
FROM pg_attribute AS idx
  JOIN pg_class AS i
    ON i.oid = idx.attrelid 
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
WHERE nspname='public';

Answer 1

To filter out system attributes, add attnum > 0. To filter out non-tables, add relkind = 'r'. To it would look like

SELECT
  ns.nspname               AS schema_name,
  idx.attrelid :: REGCLASS AS table_name,
  idx.attname              AS column_name     
FROM pg_attribute AS idx
  JOIN pg_class AS i
    ON i.oid = idx.attrelid 
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
WHERE nspname='public' AND attnum > 0 AND relkind = 'r';

You could also use the information schema, which is a bit more user-friendly in this aspect:

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public';