[Introspect Table Column DataTypes in PostgreSQL] View listing all tables joined with columns and data types for PostgreSQL

table_columns.sql

CREATE OR REPLACE VIEW table_columns AS
WITH table_oids AS (
		SELECT c.relname, c.oid
		FROM pg_catalog.pg_class c
		    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
		WHERE 
			pg_catalog.pg_table_is_visible(c.oid)),
	column_types AS (
		SELECT
		    toids.relname AS "tablename", 
		    a.attname as "column",
		    pg_catalog.format_type(a.atttypid, a.atttypmod) as "datatype"
		FROM
		    pg_catalog.pg_attribute a, table_oids toids
		WHERE
		    a.attnum > 0
		    AND NOT a.attisdropped
		    AND a.attrelid = toids.oid)
    SELECT * FROM column_types;

-- Example
SELECT * FROM table_columns WHERE tablename ~ 'dic_'

https://www.postgresql.org/docs/current/catalog-pg-attribute.html
https://www.postgresql.org/docs/current/catalog-pg-class.html
pgsql views for introspection. I have picked out a lot of the columns that think may be useful for developers to do stuff
also note this thing may simplify some of the below at some point. SO post

SELECT * FROM information_schema.tables

Note that this set of views does not filter out index tables and the pg_catolog some modification may be required to limit the results to exactly what you want. parhaps joining with information_schema.tables left join v_introspected_tables.

Usage

SELECT * FROM v_introspect_schemas;
SELECT * FROM v_introspect_tables;
SELECT * FROM v_introspect_columns;
SELECT * FROM v_introspect_table_oids;

SELECT * FROM v_introspect_tables WHERE schema = 'public' AND relkind IN ('r', 'v');

Creation

-- v_introspect_oids
-- here we get all tables names then all columns then group by table then schema

DROP VIEW IF EXISTS v_introspect_schemas;
DROP VIEW IF EXISTS v_introspect_tables;
DROP VIEW IF EXISTS v_introspect_columns;
DROP VIEW IF EXISTS v_introspect_table_oids;
CREATE VIEW v_introspect_table_oids AS
(
WITH table_oids AS (
    SELECT c.relname,
           c.oid,
           nspname,
           reltuples,
           relkind,
           relam,
           nspacl,
           relacl,
           reltype,
           relowner,
           relhasindex
    FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE pg_catalog.PG_TABLE_IS_VISIBLE(c.oid)
)
SELECT *
FROM table_oids
ORDER BY nspname, relname);
COMMENT ON VIEW v_introspect_table_oids IS 'This table contains all objects in the pg catalog and visible namespace/schemas (probably public)';
-- v_introspect_columns
CREATE VIEW v_introspect_columns AS
(
SELECT toids.relname                                                  AS "tablename",
       a.attname                                                      AS "column",
       pg_catalog.FORMAT_TYPE(a.atttypid, a.atttypmod)                AS "datatype",
       nspname                                                        AS "schema",
       oid,
       ROW_NUMBER() OVER (ORDER BY nspname DESC ,"relname", "attnum") AS column_id,
--        toids.nspacl                                    AS "nspacl",
--        toids.relacl                                    AS "tableacl",
       attnum                                                         AS "pos",
       atttypid                                                       AS "typeid",
       attlen                                                         AS "typelen",
       atttypmod                                                      AS "typemod",
       attndims                                                       AS "ndims",
       attnotnull                                                     AS "notnull",
       atthasdef                                                      AS "hasdefault",
       atthasmissing                                                  AS "hasmissing"
FROM pg_catalog.pg_attribute a,
     v_introspect_table_oids toids
WHERE a.attnum > 0
  AND NOT a.attisdropped
  AND a.attrelid = toids.oid
ORDER BY schema DESC, tablename, pos
    );
COMMENT ON VIEW v_introspect_columns IS 'This view contains a row for every single column in pg_catalog and public (or query pool schema). I have added some nice names but left all pg data for nonlimiting aplication of end API. todo remove useless fields and simplify';

-- v_introspect_tables
CREATE VIEW v_introspect_tables AS
(
SELECT tablename, -- here we could add other fields to  our column definition
       col.oid,
       schema,    -- add some table info back in
       reltuples,
       relkind,
       relam, /*nspacl,*/
       relacl,
       reltype,
       relowner,
       relhasindex,
       JSON_AGG(JSON_BUILD_OBJECT( -- condense columns into object
               'column', "column",
               'datatype', datatype,
               'table', tablename,
               'pos', pos,
               'typeid', typeid,
               'typelen', typelen,
               'typemod', "typemod",
               'notnull', "notnull",
               'hasdefault', hasdefault,
               'hasmissing', hasmissing,
               'parent_id', col.oid
           ))
           AS "columns"
FROM v_introspect_columns col,
     v_introspect_table_oids ts
WHERE ts.oid = col.oid
-- WHERE -- tablename NOT LIKE 'pg%' AND
--       tablename LIKE '%orders%'
GROUP BY tablename, col.oid, "schema", reltuples, relkind, relam, nspacl, relacl, reltype, relowner, relhasindex
ORDER BY "schema" DESC, tablename, relkind
    );
COMMENT ON VIEW v_introspect_tables IS 'Ok now we have all the columns and can group them by the table. json aggregates the columns ';

-- v_introspect_schemas
CREATE VIEW v_introspect_schemas AS
( -- lets bundle it all up at the top level, we could ad more info here maybe
SELECT "schema",
       ROW_NUMBER() OVER (ORDER BY "schema")         AS id,
       JSON_AGG(JSON_BUILD_OBJECT('id', oid,
                                  'tablename', tablename,
                                  'columns', "columns",
                                  'schema', schema)) AS "tables"
FROM v_introspect_tables
GROUP BY "schema"
ORDER BY "schema"
    );
COMMENT ON VIEW v_introspect_schemas IS 'just another aggregation to group by schema, if for example, we dont care about public ';

Sample row from v_introspect_tables

activity = {
    "tablename": "activity",
    "oid": 16490,
    "schema": "public",
    "reltuples": 1096,
    "relkind": "r",
    "relam": 2,
    "relacl": "{geoadmin=a*r*w*d*D*x*t*/geoadmin}",
    "reltype": 16492,
    "relowner": 16385,
    "relhasindex": true,
    "columns": [
      {
        "column": "id",
        "datatype": "uuid",
        "table": "activity",
        "pos": 1,
        "typeid": "2950",
        "typelen": 16,
        "typemod": -1,
        "notnull": true,
        "hasdefault": true,
        "hasmissing": false,
        "parent_id": "16490"
      },
      {
        "column": "user_id",
        "datatype": "uuid",
        "table": "activity",
        "pos": 2,
        "typeid": "2950",
        "typelen": 16,
        "typemod": -1,
        "notnull": true,
        "hasdefault": false,
        "hasmissing": false,
        "parent_id": "16490"
      },
      {
        "column": "event_time",
        "datatype": "timestamp without time zone",
        "table": "activity",
        "pos": 3,
        "typeid": "1114",
        "typelen": 8,
        "typemod": -1,
        "notnull": false,
        "hasdefault": false,
        "hasmissing": false,
        "parent_id": "16490"
      },
      {
        "column": "event_type",
        "datatype": "character varying(40)",
        "table": "activity",
        "pos": 4,
        "typeid": "1043",
        "typelen": -1,
        "typemod": 44,
        "notnull": false,
        "hasdefault": false,
        "hasmissing": false,
        "parent_id": "16490"
      },
      {
        "column": "meta",
        "datatype": "json",
        "table": "activity",
        "pos": 5,
        "typeid": "114",
        "typelen": -1,
        "typemod": -1,
        "notnull": false,
        "hasdefault": true,
        "hasmissing": false,
        "parent_id": "16490"
      }
    ]
  }