Run the following SQL script on your Postgres database. This is amazing for generating simple HTML tables for table definitions! Updated to also work for partitioned tables.
-- This script generates a multi-row result set, where each row represents one table
-- in the specified schema. Each row contains the table name, the DDL (CREATE TABLE),
-- the DML generation query instruction, and an HTML representation of the schema.
-- The 'HTML' column generates a clean HTML snippet using minimal inline styles,
-- relying on border-collapse and explicit per-cell borders for structure.
WITH
-- 1. Identify all regular tables AND partition master tables (relkind = 'r' or 'p'),
-- but excluding individual partition children (relispartition = true).
target_tables AS (
SELECT
c.relname AS table_name,
n.nspname AS schema_name
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace
WHERE
n.nspname = 'public' -- <<< CHANGE SCHEMA NAME HERE IF NEEDED
-- INCLUDE 'r' (regular) AND 'p' (partitioned master)
AND c.relkind IN ('r', 'p')
-- EXCLUDE tables that are *themselves* partitions (children)
AND NOT c.relispartition
),
-- 2. Generate the full DDL (schema), the DML generation query string, and the HTML table for EACH table.
table_scripts AS (
SELECT
t.schema_name,
t.table_name,
-- Generate DDL (CREATE TABLE statement, including columns, types, and constraints)
(
SELECT
-- NOTE: This pure SQL DDL generator CANNOT include the "PARTITION BY" clause for master tables.
-- This must be added manually if the table is a partition master.
E'CREATE TABLE ' || quote_ident(t.schema_name) || '.' || quote_ident(t.table_name) || E' (\n' ||
STRING_AGG(
-- Construct column definition: name type(length) NOT NULL DEFAULT value
E' ' || quote_ident(c.column_name) || ' ' || c.data_type ||
CASE WHEN c.character_maximum_length IS NOT NULL THEN '(' || c.character_maximum_length || ')' ELSE '' END ||
CASE WHEN c.datetime_precision IS NOT NULL AND c.data_type LIKE 'timestamp%' THEN '(' || c.datetime_precision || ')' ELSE '' END ||
CASE WHEN c.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END ||
CASE WHEN c.column_default IS NOT NULL THEN ' DEFAULT ' || c.column_default ELSE '' END
, E',\n' ORDER BY c.ordinal_position
) ||
E'\n);'
FROM
information_schema.columns c
WHERE
c.table_schema = t.schema_name
AND c.table_name = t.table_name
) AS ddl_content,
-- Generate the DML GENERATION QUERY (as a string)
FORMAT(
-- This SELECT statement will be copied by the user and run separately.
E'SELECT E''-- Data for %I.%I\n'' || STRING_AGG(E''INSERT INTO %I.%I VALUES ('' || \n' ||
E' array_to_string(ARRAY(SELECT quote_literal(a) FROM unnest(array_agg(t.*::text)) AS a(a)), '','') || E'');'', E''\n'' ORDER BY 1)\n' ||
E'FROM %I.%I t;',
t.schema_name, t.table_name, -- Header text
t.schema_name, t.table_name, -- INSERT statement text
t.schema_name, t.table_name -- FROM clause for data source
) AS dml_generation_query,
-- Generate the HTML table content using minimal inline styles
(
SELECT
-- Construct the table using inline styles: border-collapse is crucial for uniform borders
-- Removed font-family for inheritance
E'<table style="border-collapse: collapse; border: 1px solid black; width: 100%; margin: 20px 0; box-shadow: 0 2px 4px rgba(0,0,0,0.1);">\n' ||
E' <caption style="padding: 10px; font-weight: bold; background-color: #f0f0f0; border: 1px solid black; border-bottom: none;">Schema for Table: ' || quote_ident(t.table_name) || E'</caption>\n' ||
E' <tr>\n' ||
-- Table Headers (TH): Structural styles (border/padding) and custom color
E' <th style="padding: 12px; text-align: left; border: 1px solid #ddd; background-color: #E48312; color: white;">Name</th>\n' ||
E' <th style="padding: 12px; text-align: left; border: 1px solid #ddd; background-color: #E48312; color: white;">Type</th>\n' ||
E' <th style="padding: 12px; text-align: left; border: 1px solid #ddd; background-color: #E48312; color: white;">Description</th>\n' ||
E' </tr>\n' ||
-- Table Data (TD): Structural styles (border/padding) remain.
STRING_AGG(
E' <tr>\n' ||
-- Name cell: Base styles
E' <td style="padding: 8px; border: 1px solid #ddd;">' || c.column_name || E'</td>\n' ||
-- Type cell: Base styles only
E' <td style="padding: 8px; border: 1px solid #ddd;">' || c.data_type ||
CASE WHEN c.character_maximum_length IS NOT NULL THEN '(' || c.character_maximum_length || ')' ELSE '' END ||
E'</td>\n' ||
-- Desc cell: Base styles only
E' <td style="padding: 8px; border: 1px solid #ddd; color: #555;">' || COALESCE(d.description, '') || E'</td>\n' ||
E' </tr>'
, E'\n' ORDER BY c.ordinal_position
) ||
E'\n</table>'
FROM
information_schema.columns c
-- Join system tables to fetch the column comments (descriptions)
LEFT JOIN
pg_class cls ON cls.relname = t.table_name AND cls.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = t.schema_name)
LEFT JOIN
pg_attribute attr ON attr.attrelid = cls.oid AND attr.attname = c.column_name
LEFT JOIN
pg_description d ON d.objoid = cls.oid AND d.objsubid = attr.attnum
WHERE
c.table_schema = t.schema_name
AND c.table_name = t.table_name
) AS html_content -- end of HTML logic
FROM
target_tables t
)
-- 3. Combine DDL, the DML generation query, and HTML content into the final required format.
SELECT
s.table_name AS "Table Name",
s.html_content AS "HTML",
E'-- START TABLE BACKUP SCRIPT: ' || s.schema_name || '.' || s.table_name || E' (Generated on ' || now() || E')\n\n' ||
-- DDL Section
E'-- DDL: Create Table Structure\n' ||
s.ddl_content || E'\n\n' ||
-- DML Section (Requires a second step)
E'-- DML: Data Generation Instructions\n' ||
E'-- ----------------------------------------------------------------------------------------------------------------------------------\n' ||
E'-- COPY and EXECUTE the following SELECT query in the Query Tool to generate the actual INSERT statements for the data:\n' ||
E'-- ----------------------------------------------------------------------------------------------------------------------------------\n' ||
s.dml_generation_query || E'\n' ||
E'-- ----------------------------------------------------------------------------------------------------------------------------------\n\n' ||
E'-- END TABLE BACKUP SCRIPT: ' || s.schema_name || '.' || s.table_name || E'\n' AS "SQL"
FROM
table_scripts s
ORDER BY
"Table Name";