Postgres general notes

Copy a schema from one schema to another

This is how I pretty easily copied a Postgres schema structure and data to another schema (the specific example is copying the public schema from Supabase to a local WSL Postgres instance.

Using Navicat, right click the source schema and select "Dump SQL File" for "Structure and Data." This creates an SQL file that recreates the database.

This is probably a good poor-man's backup as well.

The file created has the schema hard-cored throughout. Carefully, do a search-and-replace to populate the SQL with the correct target schema name.

Ideally, after making that schema name change, you'd copy this SQL into a query window for the target schema and click run. Doing that caused issues for me because the views weren't created (perhaps before they were needed).

To work around that

1. Run the SQL and let it fail. In my case, it did copy everything by the views.
2. Find the places in the SQL where the views are created, and run that part separately.

In the case of copying the schema SQL from Supabase to the Postgres WSL instance, that worked to pull the downloads DB down from Supabase into the WSL Postgres instance. To ensure that all is good, use the SQL in the next section to ensure the row counts for both tables and views is correct.

Audit row counts after schema copy

Show row counts for every table and view in a schema. The schema must provided in each query (note how it as provided the public schema is used.

Get table row counts

SELECT 
    table\\\_name AS "table name",
    \\\(xpath\\\('/row/cnt/text\\\(\\\)', 
        query\\\_to\\\_xml\\\(format\\\('SELECT count\\\(\\\*\\\) AS cnt FROM %I.%I', table\\\_schema, table\\\_name\\\), false, true, ''\\\)
    \\\)\\\)[1]::text::bigint AS "row count"
FROM 
    information\\\_schema.tables
WHERE 
    table\\\_schema = 'public' -- Replace 'public' with your schema name
    AND table\\\_type = 'BASE TABLE'
ORDER BY 
    table\\\_name;

Google Gemini created this SQL. About the one above, it said:

"If you need the exact number of rows and your tables are not so large that a full scan would cause performance issues, you can use this query. It uses a clever XML trick to run a sub-count for every table in one go without needing to write a separate function."

Get view row counts

SELECT 
    table\\\_name AS "view name",
    \\\(xpath\\\('/row/cnt/text\\\(\\\)', 
        query\\\_to\\\_xml\\\(format\\\('SELECT count\\\(\\\*\\\) AS cnt FROM %I.%I', table\\\_schema, table\\\_name\\\), false, true, ''\\\)
    \\\)\\\)[1]::text::bigint AS "row count"
FROM 
    information\\\_schema.views
WHERE 
    table\\\_schema = 'public' -- Replace 'public' with your schema name
ORDER BY 
    table\\\_name;