Postgres Schema for Prompting

Whenever I need to write a query for one-off tasks on my postgres db, I ask chatgpt / claude to generate the initial query for me. The most annoying part of this process is giving them an up-to-date schema of my db (which in less mature apps is changing all the time), so I made a postgres query that provides a string representation of all the schemas, tables, columns, and foreign keys in my db.

WITH table_info AS (
    SELECT 
        table_schema,
        table_name,
        array_agg(column_name::text ORDER BY ordinal_position) AS columns
    FROM 
        information_schema.columns
    GROUP BY 
        table_schema, table_name
),
record_count AS (
    SELECT 
        relname AS table_name,
        n_live_tup AS row_count
    FROM 
        pg_stat_user_tables
),
foreign_keys AS (
    SELECT
        tc.table_name,
        array_agg(
            kcu.column_name || ' -> ' || 
            ccu.table_schema || '.' || ccu.table_name || '.' || ccu.column_name
            ORDER BY kcu.ordinal_position
        ) AS foreign_keys
    FROM 
        information_schema.table_constraints tc
    JOIN 
        information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    JOIN 
        information_schema.constraint_column_usage ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE 
        tc.constraint_type = 'FOREIGN KEY'
    GROUP BY 
        tc.table_name
)
SELECT 
    t.table_schema,
    t.table_name,
    t.columns,
    r.row_count,
    COALESCE(f.foreign_keys, ARRAY[]::text[]) AS foreign_keys
FROM 
    table_info t
JOIN 
    record_count r ON t.table_name = r.table_name
LEFT JOIN
    foreign_keys f ON t.table_name = f.table_name

The output of this is a table with the following columns:

• table_schema: The schema of the table
• table_name: The name of the table
• columns: The columns in the table (as an array of strings)
• row_count: The number of rows in the table
• foreign_keys: The foreign keys for the table (as an array of strings in the format column_name -> referenced_schema.referenced_table.referenced_column_name)

To run this I wrap the query and save it to llm_schema.sql:

COPY (
    ...
) TO STDOUT WITH CSV HEADER;

The output of this command is pasted into the prompt:

psql ... -f llm_schema.sql | pbcopy

Other notes:

• If you have an older db with some tables that aren’t used anymore row count is helpful for disambiguating that for the model. Be sure to run VACUUM ANALYZE to keep the values up to date
• Since I use this for one-off queries, I don’t include indexes because performance is rarely a concern for this use case, but including them should be straightforward