Chapter 6

Structure

See and change how a table is built: its columns, keys, indexes and triggers. Structure changes are staged like data edits and committed the same way, so you can review the DDL before it runs.

The Structure view#

To see a table's structure, click Structure in the bottom bar, or press Ctrl+Alt+]. Ctrl+Alt+[ goes back to its data. You can also right-click a table in the sidebar and choose Open Structure.

At the top are the table's Name, its Primary key and its comment, with a search field for tables with many columns. Below are the columns, with column_name, data_type, is_nullable, column_default, foreign_key and comment, then the table's constraints.

The bottom bar's Index, Triggers and DDL show the rest of the table's definition.

Columns, keys and defaults#

Every cell in the columns list can be edited in place. Double-click it, or select it and press Enter.

Name: Type a new name to rename the column.
Type: Pick from common types, your domains and enums, or type any PostgreSQL type, such as numeric(12,2).
Nullable: Click the checkbox, or press Space, to allow or forbid NULL.
Default: Pick a common default, such as now() or an identity, or type an expression.
Foreign key: Opens the foreign key sheet: the Columns, the Referenced Table and Referenced Columns, what happens On Update and On Delete, and its Name. Delete removes the key.
Comment: A note stored with the column.

Cells with a menu show an arrow; click it, or press Alt+Enter, to open the menu with its list already showing.

To add a column, press Ctrl+I, click + in the bottom bar, or double-click below the last column.
To drop a column, select it and press Delete. It's marked until you commit; press Delete again, or choose Restore Column, to keep it.
To change the primary key, open the Primary menu at the top and check the columns it should cover. A primary key column is always NOT NULL.

Two columns selected in the Structure view, with the right sidebar showing a form where values differ — With several columns selected, the right sidebar edits them together.

Select several columns and press Space to edit their type, nullability, default and comment together in the right sidebar. With one column selected, the sidebar lists what uses it, such as indexes and keys.

Indexes, triggers and DDL#

Index#

Click Index in the bottom bar to see the table's indexes, with their columns or expressions, method, whether they're unique, and any condition.

Choose New Index, or double-click below the list, to add one. Set its name, columns, method (such as btree or gin), whether it's unique, and an optional condition for a partial index.
Check concurrently to build it without locking the table. Rowan runs that statement after the transaction, since PostgreSQL requires it, and the preview says so.
Changing an index drops it and creates it again.

Triggers#

Click Triggers to see the table's triggers: name, timing, events, level, the function it calls, its condition, and whether it's enabled.

Choose New Trigger to add one. The function menu lists trigger functions first. In PostgreSQL a trigger always calls a function; to change what it does, open the function from the sidebar and edit its source.

DDL#

Click DDL to see the server's own definition of the table, with its indexes and comments. Press Ctrl+Shift+C, or click Copy, to copy it.

The DDL view showing CREATE TABLE public.orders with its columns, constraints, indexes and comments — The DDL view shows the table as CREATE statements.

Commit structure changes#

Structure changes are staged with your data edits. The Preview shows the DDL Rowan will run, structure first, then rows, all in one transaction. Commit with Ctrl+S, discard with Ctrl+Shift+Delete, and undo with Ctrl+Z. See The code preview.

Create, rename, clone, truncate and delete tables#

Create a table#

Click + at the bottom of the left sidebar and choose New Table….
Enter its name and choose Create. A Structure tab opens with an id identity column as its primary key.
Add the columns you need.
Press Ctrl+S. Until you commit, the table doesn't exist on the server; discarding closes its tab.

Other table actions#

Right-click a table in the sidebar:

Rename: Change the Name at the top of its Structure view, then commit.
Clone…: Makes a copy named table_clone, with Duplicate table content on to copy its rows too.
Truncate…: Removes every row, optionally with Cascade and Restart identity.
Delete…: Drops the table, optionally with Cascade.
Copy Script As: Copies SQL for the table: Creation, Select Top 100, Insert, Truncate or Drop.

Each is staged until you commit. Pressing Delete on a selected table in the sidebar stages a drop straight away, and Alt+Delete stages a truncate. They still wait for Ctrl+S.

Views, materialized views, functions and schemas#

Views: New View… opens a template in an SQL editor. To change a view, right-click it and choose Edit View…: its CREATE OR REPLACE VIEW opens in an editor, ready to change and run.
Materialized views: New Materialized View… works the same way. Refresh in its menu runs REFRESH MATERIALIZED VIEW.
Functions and procedures: Open one to see its full source in an editor. New Function… starts from a template. Delete drops it by its argument types, so overloaded functions stay apart.
Sequences and types: Open one to see its CREATE SEQUENCE, CREATE TYPE or CREATE DOMAIN statement.
Schemas: The schema menu at the bottom of the sidebar has New Schema…, Rename and Delete.

Click Tables, Views or Functions in the sidebar for an overview of everything of that kind in the schema, with row estimates, sizes including indexes, and comments. Double-click one to open it.