Scripts

pgjumbo writes SQL for you in four places: Script as on an object, the Script menu beside every script it generates, Generate Scripts for many objects of a database at once, and the Schema diff, which writes what makes one schema match another. Nothing it writes runs by itself. The script opens in a Query Tool, goes to the clipboard or into a file, and you decide what to run.

Script an object

Right-click an object in the Explorer and choose Script as. pgjumbo writes the statement and opens it in a new Query Tool.

• 1 Script as is in the first group of an object’s right-click menu, after Query Tool (and View Data on anything with rows). Folders, the tools under Management and server rows do not have it.
• 2 CREATE writes the object’s own script: the same text as the SQL tab of the Inspector, with its owner, comments and grants.
• 3 DROP and CREATE puts the statement that Drop… would run in front of the CREATE script, for when you want to build the object again.
• 4 SELECT names every column of the table or view.
• 5 INSERT, UPDATE and DELETE are statements on the table’s rows, with a comment wherever you have a value to fill in.

The choices depend on the kind of object:

Choice	Offered on	What pgjumbo writes
CREATE	Every object in the tree: databases, schemas, tables, columns, constraints, indexes, roles, tablespaces, extensions and every other kind, system objects too	The script that creates it, as the Inspector shows it. For a column, ALTER TABLE … ADD COLUMN (and its COMMENT ON COLUMN).
DROP and CREATE	The objects that Drop… is offered for, except system objects	The DROP statement without CASCADE, a blank line, then the CREATE script.
SELECT	Tables, partitioned tables, views, materialized views and foreign tables, system ones too	SELECT with every column in order, then FROM and the name. A relation with no columns gets SELECT *.
INSERT	Tables, partitioned tables, views and foreign tables (not materialized views or system objects)	INSERT INTO with the columns you can set, and VALUES with a comment for each: its name and type.
UPDATE	The same as INSERT	UPDATE … SET with each column you can set and its type as a comment, and WHERE /* which rows */.
DELETE	The same as INSERT	DELETE FROM the name, and WHERE /* which rows */.
EXECUTE	Functions and procedures, system ones too (not aggregates)	A call with a comment for each argument: SELECT * FROM the function, or CALL the procedure.

Databases, roles, tablespaces, columns, subscriptions, foreign servers and user mappings offer only CREATE, since Drop… is not offered for them.

Names come out as PostgreSQL quotes them, so a table called Order Lines appears as public."Order Lines".

What the statements look like

• 1 Each script opens in a new Query Tool tab, here Query 1 · shop. The tab has a connection of its own, like any Query Tool.
• 2 The tab is on the object’s database. An object that belongs to the whole server, such as a role or a tablespace, opens on the database the server’s connection names.
• 3 Each column you can set gets its type as a comment, where the value goes.
• 4 WHERE /* which rows */ waits for your condition.
• 5 Nothing has run: the Messages tab has no result until you click Run.

The other choices follow the same pattern. For the orders table above, a function and a procedure, they write:

INSERT INTO public.orders (id, customer_id, created_at, total)
VALUES (/* id bigint */, /* customer_id bigint */, /* created_at timestamp with time zone */, /* total numeric(12,2) */);

DELETE FROM public.orders
 WHERE /* which rows */;

SELECT *
  FROM public.customer_total(/* customer bigint */);

CALL public.archive_orders(/* before date */, NULL /* moved integer, OUT */);

A few rules decide what goes in:

• Generated columns and identity columns GENERATED ALWAYS are left out of INSERT and UPDATE, because PostgreSQL sets them. A table with no other column gets INSERT INTO … DEFAULT VALUES;.
• A function’s call lists its input arguments (IN and INOUT), each as a comment with its name and type. A procedure’s OUT arguments appear as NULL, as CALL wants them. A VARIADIC argument is written as VARIADIC /* … */.
• The placeholders are comments, so an INSERT, UPDATE or DELETE does not run as it stands: PostgreSQL reports a syntax error until you replace each comment with a value or a condition.

When pgjumbo cannot write it

If a script cannot be written, a red notice beside the row says why, and no tab opens. For example:

Notice	Why
pgjumbo has no CREATE script for this object.	pgjumbo does not script this kind of object.
public.t has no column an UPDATE can set.	Every column is generated or an identity column GENERATED ALWAYS.
That object is gone. Refresh the Explorer.	Someone dropped the table or view since the tree was read.
That routine is gone. Refresh the Explorer.	The same, for EXECUTE on a function or procedure.
This object no longer exists. Refresh the tree.	The same, for CREATE or DROP and CREATE (DROP and CREATE ends with Refresh the Explorer.).
… is built into PostgreSQL; pgjumbo does not drop it.	DROP and CREATE on a built-in object, such as the plpgsql extension.

The notice stays until you press Esc, click elsewhere or click its ×. The notice beside the row explains it.

Script several objects at once

In Object Details, tick the rows you want and use the Script as button.

• 1 Tick the objects.
• 2 The count of ticked rows, followed by Script as and the other actions for them.
• 3 The menu offers only the choices that every ticked object has.
• 4 pgjumbo writes each object’s statement in turn and opens them all in one new Query Tool, a blank line apart, on the folder’s database.

While it writes, the Script as button is greyed out. If one script fails, the reason shows above the list and no tab opens.

The Script menu

Every script that pgjumbo generates before running anything has a Script menu beside it. Use it to take the script elsewhere instead of letting pgjumbo run it.

• 1 Script opens the menu. It is greyed out while the script is still being written, or when it could not be written.
• 2 Open in Query Tool puts the script in a new Query Tool tab on the object’s database. A dialog or sheet it came from closes, so that you see the new tab.
• 3 Copy puts the script on the clipboard. A note beside the button says Copied for a few seconds, or The clipboard is not available if copying failed. When opening or saving fails, the same note says why.
• 4 Save to file… opens the system’s Save SQL File dialog with a name filled in, such as drop customers.sql. After saving, the note says Saved as and the file’s name. The file ends with a line break and joins the recent files on the start page.

The menu appears in these places:

Where	The script
The Drop… and Truncate… dialogs	The statement, with the options you ticked. See Drop, truncate and other quick actions.
The Maintenance… dialog	The VACUUM, ANALYZE, REINDEX or other command. See Maintenance and jobs.
Install extension…	The CREATE EXTENSION statement. See Extensions.
The dialogs that create a database, schema, view, materialized view, sequence or index, and Edit index…	The script that creates or changes the object.
Generated SQL under an object tab’s view with changes, and the review sheet	The statements Save would run. On the review sheet a line beside the menu says Open, copy or save these so many statements instead. See Object tabs and review.
The SQL view of a new table, role, function or procedure, before its first Save	The CREATE statement Save would run.
Run… on a function or procedure	The call it builds. See Functions and procedures.
A role’s Owned objects	REASSIGN OWNED or DROP OWNED. See Roles.
The draft in Configuration	The ALTER SYSTEM statements. See Server configuration.
A scheduled job’s dialog	The pg_cron call. See Scheduled jobs.

In the Inspector’s SQL tab, and in the SQL view of an object that already exists, the menu has only Copy and Save to file…, and an Open in Query Tool button sits beside it. The file name it offers there is the object’s name.

Generate scripts for a database

Generate Scripts writes many objects of one database as a single script, in the order PostgreSQL needs to create them. Use it to copy a schema’s structure to another server, to keep a database’s objects in version control, or to hand a colleague the SQL for a set of tables.

Open it in either of these ways:

• Right-click a database or a schema in the Explorer and choose Tasks▸Generate Scripts…. On a database every object starts ticked; on a schema, that schema’s objects.
• Select a database or a schema in the Explorer and choose Tools▸Generate Scripts… in the menu bar. The item is greyed out while anything else is selected.

The server must be connected. System databases and schemas do not offer it. Neither way has a key.

• 1 Objects counts what is ticked out of what the database has.
• 2 Filter shows only the objects whose name contains what you type, upper or lower case alike. Ticking a group then ticks only the objects shown.
• 3 Each group (Extensions, a schema, a folder in it) has a box that ticks or unticks every object under it, and says how many of them are ticked. A box half filled means some are. The arrow opens or closes the group.
• 4 What to write chooses which statements go in.
• 5 Where to chooses where the script goes.
• 6 Generated SQL shows the first six lines of the script as it will be, and how many lines it has. Show all shows up to 500 lines (Show less goes back), and Copy copies the whole script.
• 7 Generate script writes it and sends it where you chose. It is greyed out while nothing is ticked, and says Generating… while it works.

Under the title, the server’s environment tag, the server and the database come before tick the objects; their scripts come out in the order PostgreSQL needs. While the list loads, the dialog says Reading the database’s objects….

The preview follows your choices about a third of a second after you stop changing them. It says Tick the objects to script. while nothing is ticked, Writing the script… before the first preview arrives, and shows the error in red when the script cannot be written. The footer says Nothing runs: the script opens for you to read., or shows what went wrong (a failed Copy says Could not copy: and why). ↩︎ (Enter on Windows and Linux) in the Filter field does the same as Generate script. Cancel, × or Esc closes the dialog.

The objects you can tick

The list holds the database’s extensions (all but plpgsql), then each schema in alphabetical order with its objects in folders: Tables (partitions too), Views, Materialized views, Functions, Procedures, Aggregates, Sequences, Types (enum, composite and range types), Domains, Collations, Operators, Text search configurations and Text search dictionaries. A folder appears only when the schema has objects of that kind.

Some objects are not in the list:

• System schemas (pg_catalog, information_schema, pg_toast, temporary schemas) and anything that belongs to an extension. The extension’s own CREATE EXTENSION brings those back.
• Indexes, constraints, triggers, policies, rules and the sequences a column owns. They come with their table’s script.
• Foreign tables, base types and multirange types, text search parsers and templates, and the objects that live outside a schema other than extensions: casts, event triggers, languages, publications, subscriptions, foreign-data wrappers, foreign servers, roles and tablespaces. Script those one at a time with Script as.

pgjumbo adds the schemas your objects are in, and nothing else. An object that needs another one you left unticked, such as a view on a table you did not tick, fails when the script runs unless that object is already there.

If the database has nothing to script, the list says This database has no objects to script. If the filter matches nothing, it says No object matches.

What to write

Option	Starts	What it adds
CREATE statements	Ticked	The statements that make each object, and everything in its script that is not an owner, a privilege or a comment (indexes, constraints, ALTER SEQUENCE … OWNED BY, and so on). The foreign keys and SET check_function_bodies come only with this option.
DROP … IF EXISTS before each object	Not ticked	A DROP … IF EXISTS for each object at the top, dependents first, for a script that rebuilds what is there.
Owners	Ticked	The ALTER … OWNER TO statements.
Privileges	Ticked	The GRANT and REVOKE statements that differ from PostgreSQL’s defaults.
Comments	Ticked	The COMMENT ON statements.
Rows, as INSERT statements	Not ticked	The first 1,000 rows of each table, as INSERT statements.
In one transaction	Ticked	BEGIN; at the start and COMMIT; at the end, so the script makes everything or nothing.

The grey line under an option repeats what it adds, such as ALTER … OWNER TO under Owners. Untick everything but Privileges, for example, to get only the grants on the objects you ticked.

Where the script goes

Choice	What happens
A new Query Tool (the default)	The script opens in a new Query Tool tab on the database, and the dialog closes.
One .sql file…	The Save SQL File dialog opens with the database’s name filled in, such as shop.sql. A notice beside the row says Saved the script to and the file’s name.
A file per object, in a folder…	A folder dialog, Folder for the Scripts, opens. pgjumbo writes one .sql file per object, named by its place in the script and its name, such as 001 public.sql for a schema and 007 public.customers.sql for a table, and a last file for the foreign keys, numbered after the others and called … foreign keys.sql. Files of the same name in that folder are replaced. The notice says Wrote so many files to and the folder’s name.
The clipboard	The whole script is copied. The notice says Copied the script of so many objects.

In every case but A new Query Tool, the dialog closes and a green notice beside the row in the Explorer says where the script went. It goes away after six seconds.

• 1 The database or schema you opened the dialog on.
• 2 The notice: here, 16 files (two schemas and 14 objects) written to a folder called scripts. Its × closes it sooner.

If you cancel the file or folder dialog, you are back in the Generate scripts dialog with your choices kept. In a file per object, each file holds that object’s statements (and its DROP … IF EXISTS and rows when ticked, and SET check_function_bodies = false; for a function or procedure), but no BEGIN or COMMIT. A character that a file name cannot hold, such as a bracket in a function’s arguments, becomes _.

What the script contains

pgjumbo writes the script in this order:

1. A comment that says how many objects it holds (the schemas count too) and that nothing has run.
2. When it creates a function or procedure, SET check_function_bodies = false;, so that a function can mention a table made further down.
3. BEGIN;, with In one transaction.
4. With DROP … IF EXISTS, a section that removes what is there now: first the foreign keys, then each object, dependents before what they depend on. Schemas are never dropped.
5. Each schema’s CREATE SCHEMA IF NOT EXISTS, then each object under a comment that names it. Every object comes after what it needs, as PostgreSQL’s catalog records it, and a partition after its parent. Where nothing decides, the order is: extensions; collations, types and text search dictionaries; domains, sequences and text search configurations; functions and procedures; aggregates and operators; tables; views; materialized views.
6. With Rows, the rows of each table: up to the first 1,000 rows the server returns, in no set order. The server quotes the values. Generated columns are left out, and a table with an identity column GENERATED ALWAYS gets OVERRIDING SYSTEM VALUE. pgjumbo reads only the table’s own rows, so a partitioned table’s rows come with its partitions, when you tick them, and a parent table’s without its children’s.
7. The foreign keys, added once every table is there. That way two tables can point at each other, and the rows can go in before the keys check them.
8. COMMIT;, with In one transaction.

For two tables in a schema sales, with DROP … IF EXISTS and Rows ticked, the script looks like this (shortened):

-- 3 objects from shop, in the order PostgreSQL needs them. Written by pgjumbo; nothing has run.

BEGIN;

-- What is there now, dependents first.
ALTER TABLE IF EXISTS sales.orders DROP CONSTRAINT IF EXISTS orders_customer_id_fkey;
DROP TABLE IF EXISTS sales.orders;
DROP TABLE IF EXISTS sales.customers;

-- Schema sales
CREATE SCHEMA IF NOT EXISTS sales AUTHORIZATION app;

-- Table sales.customers
CREATE TABLE sales.customers (
    …
);

ALTER TABLE sales.customers OWNER TO app;

-- Table sales.orders
CREATE TABLE sales.orders (
    …
);

ALTER TABLE sales.orders OWNER TO app;

-- Rows: at most the first 1,000 of each table.

INSERT INTO sales.customers (id, name) OVERRIDING SYSTEM VALUE VALUES ('1', 'Ada');
…

-- Foreign keys, once every table is there.
ALTER TABLE sales.orders
    ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES sales.customers(id);

COMMIT;

Running a script with DROP … IF EXISTS

Run against the database it came from, such a script drops the objects with all their data. It puts rows back only with Rows ticked, and then at most 1,000 per table. Run it only on a database you mean to rebuild.

pgjumbo reads the list of objects on the Explorer’s connection. It writes the script on a connection of its own to the database, which only reads: the catalog, and with Rows the tables.

Compare two schemas

The schema diff compares a schema with the schema of the same name in another database, on the same server or on another connected one, and writes the script that would make that other one match. Use it to see what a staging database lacks compared with development, or what drifted between two copies of the same schema.

Right-click a schema in the Explorer and choose Compare Schema…, or select the schema and choose Tools▸Schema Diff…. A tab named Schema diff · shop.public opens; a schema has one such tab, and choosing it again brings that tab to the front. System schemas do not offer it.

• 1 Source: the schema you started from, with its server’s environment tag.
• 2 The target server. It starts on the source’s own server, and lists the servers that are connected now; connect another one first to compare with it.
• 3 The target database. It lists that server’s databases, without the source’s own database and without template databases; the database of the same name is chosen first when there is one. When the server has no other database, the list is empty and greyed out. After it comes the schema’s name: the target is always the schema of the same name. When that database has none, the header says That database has no schema … and Compare stays greyed out.
• 4 Compare reads both schemas and shows what differs. It says Comparing… while it reads. If the comparison fails, the reason shows in red under the header.

With no server connected the tab says Connect the server to compare with. pgjumbo reads both sides on the Explorer’s connections to those databases, and only reads.

After a comparison, choosing another target server or database leaves the last result on screen until you click Compare again; the script and Open in Query Tool still belong to the target you compared with.

Close the tab with the × on it or Close on its right-click menu; File▸Close Tab and its keys do not close a schema diff, and it cannot move to a window of its own. With the Assistant on, it knows which schema the diff in front compares.

Read the differences

• 1 The counts: changed (in both, but different), only in source, only in target and identical. At the right end, each side’s server and PostgreSQL version.
• 2 Differences, with how many objects differ: every object that differs, grouped by kind (tables, views, functions and the rest) in the order a script needs them. The mark after a name says how it differs: ~ changed, + only in the source, − only in the target; hover over it for the words. Click an object to compare it. Identical objects at the end opens the list of those that match, by name only.
• 3 Options. Changing one compares again at once.
• 4 The chosen object: its name, kind and schema, how it differs, and how many lines the source adds (+) and the target has that the source lacks (−). Below, the source’s script and the target’s side by side, with lines only in the source in green and lines only in the target in red. After Compare, the first difference is chosen for you, and the one you chose stays chosen when you compare again. When nothing differs, this part says Choose a difference to see both scripts.
• 5 Migration script: the script that makes the target match the source, and how many statements it has.
• 6 Copy puts the script on the clipboard (Copied the script). Save as .sql asks where to save it, suggesting <database>-<schema>-migration.sql with the target’s names, and then says Saved and the file’s name. Open in Query Tool opens it in a new Query Tool on the target database, where you read it and run it yourself. When the target server is tagged Production, a warning above these buttons says Target is Production — review before running.

When the two schemas match, the list says The two schemas match.

Option	Default	What it does
Ignore whitespace	Ticked	Runs of spaces and line breaks count as one space, so a function or view laid out differently is not counted as changed.
Ignore owners and privileges	Ticked	Owners, GRANT and REVOKE are not compared, and the script leaves the target’s as they are.
Include DROP statements	Unticked	Drops the objects, columns, constraints and indexes the target has and the source lacks. Unticked, those DROP statements are in the script as comments, under Only in the target (not dropped: include DROP statements to drop them).

What the script does

The script starts with comments that name both sides and say that nothing has run (and that the target is a Production server, when it is), then wraps everything in BEGIN and COMMIT:

• Only in the source: the objects the target lacks, created as Generate scripts would write them, in the order they need each other, with foreign keys after every table.
• Changed: for a table, the ALTER TABLE statements a table’s tab would run to turn the target’s table into the source’s; for a view, materialized view, sequence, function, procedure or type, what its tab would run. For anything else, and for a change pgjumbo cannot write (a function whose return type changed, for one), the section holds only a comment, Left for you to review: and why: compare the two scripts and write that change yourself.
• Only in the target: the DROP statements, live or as comments as the option says.

Before you run it

The script is a proposal. Read it in the Query Tool before you run it, above all with Include DROP statements ticked: a dropped table or column takes its data with it. A table change can take locks and rewrite a table as the table’s own review would warn you; run it at a quiet time on a busy server.