[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"
      }
    ]
  }

This gist is awesome, thx guys!

I made a slightly variation on @syonfox's to include foreign key information in v_introspect_columns (and expose it at v_introspect_tables):

-- all credit to https://gist.github.com/jarnaldich/d5952a134d89dfac48d034ed141e86c5?permalink_comment_id=4401600#gistcomment-4401600
--
-- 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');
--
-- 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 (
        WITH
            -- we'll use this CTE to track foreign keys between tables
            foreign_keys AS (
                SELECT DISTINCT
                    tc.table_schema AS schema_name,
                    kcu.table_name,
                    kcu.column_name,
                    ccu.table_name AS foreign_table_name,
                    ccu.column_name AS foreign_column_name
                FROM
                    information_schema.table_constraints AS tc
                    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
                    AND tc.table_schema = kcu.table_schema
                    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
                    AND ccu.table_schema = tc.table_schema
                WHERE
                    tc.constraint_type = 'FOREIGN KEY'
            )
        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",
            foreign_keys.foreign_table_name AS "ftable",
            foreign_keys.foreign_column_name AS "fcolumn"
        FROM
            pg_catalog.pg_attribute a,
            v_introspect_table_oids toids
            -- optionally mix in any knowledge we have about foreign keys
            LEFT JOIN LATERAL (
                SELECT
                    *
                FROM
                    foreign_keys
                WHERE
                    foreign_keys.schema_name = nspname
                    AND foreign_keys.table_name = toids.relname
                    AND foreign_keys.column_name = a.attname
            ) AS foreign_keys ON true
        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,
                    'ftable',
                    ftable,
                    'fcolumn',
                    fcolumn,
                    'parent_id',
                    col.oid
                )
            ) AS "columns"
        FROM
            v_introspect_columns col,
            v_introspect_table_oids ts
        WHERE
            ts.oid = col.oid
        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 ';

That's awesome, thank you!