Change a table's structure

You need a new column on a table, and an index to look rows up by it. A table’s Structure tab stages both, and the review shows the SQL, the lock each statement takes, and how risky it is at the table’s size, before anything runs.

On a Local or Development database you can work in Sketch instead, where structure is a draft and each change applies as you make it. This page is about the review, which every database uses outside Sketch.

Add a column

1. Open the table and go to its Structure tab.
2. Under Columns, click Add column.
3. Fill in the sheet:
   • Name: the column’s name.
   • Type: any Postgres type, such as text, integer or timestamptz.
   • Default (SQL, optional): an expression, such as now(), 0 or ''.
   • Not null: on if every row must have a value.
4. Click Add.

The change is staged, not run. A bar rises under the tab:

1 structure change staged · add column memo text

Add an index

1. Under Indexes, click Add index.
2. Click the columns in the order the index should hold them.
3. Turn on Unique if no two rows may share these values. Choose a Method if you want something other than btree. Leave Name (optional) empty to name it after the table and columns.
4. Click Add.

The bar now counts both: 2 structure changes staged.

Review the SQL

Click Review SQL in the bar.

BEGIN;
ALTER TABLE public.orders ADD COLUMN memo text DEFAULT '';
CREATE INDEX orders_customer_id_status_idx ON public.orders (customer_id, status);
COMMIT;

Alcuin quotes names where Postgres needs them. Types and defaults run exactly as you typed them.

Beside each change, the review notes what the statement does to everyone else using the table:

• The lock it takes, such as ACCESS EXCLUSIVE or SHARE. Hover the lock for what it blocks.
• Whether it rewrites the table or reads every row, and how big the table is, for example 200 MB, ≈2,000,000 rows.
• Its risk at that size: low risk, medium risk or high risk. Above low, a sentence says why.

For these two changes:

• Adding a column with a constant default, such as '', 0 or now(), is brief. It still takes an ACCESS EXCLUSIVE lock, which waits behind any open transaction on the table.
• A default that differs per row, such as gen_random_uuid() or clock_timestamp(), writes every row. The review says rewrites the table, and the risk grows with the table’s size.
• Building an index takes a SHARE lock and reads every row while writes wait. The review warns building the index takes a lock that blocks writes until it finishes.

A column added as Not null with no default gets a warning too: memo is NOT NULL with no default: this fails if the table has rows. Add a default, or leave Not null off.

Switch to the safer form

When a change has a form that blocks less, and the risk is not low, the review offers it as a button. For an index, it is Use: Build it concurrently.

Click it, and the review plans again:

BEGIN;
ALTER TABLE public.orders ADD COLUMN memo text DEFAULT '';
COMMIT;

CREATE INDEX CONCURRENTLY orders_customer_id_status_idx ON public.orders (customer_id, status);

The index now takes a SHARE UPDATE EXCLUSIVE lock, so reads and writes go on while it builds. It is marked outside the transaction, because Postgres won’t build an index concurrently inside one. If the build fails, it leaves an invalid index behind for you to drop.

Use: Build it concurrently, and the review plans again.

Apply, step by step

Click Apply 2 changes in the review, or Apply 2 structure changes in the bar, or press ⌘S on macOS, Ctrl+S on Windows and Linux.

How the changes run depends on what the review says above the SQL:

• One transaction: if any statement fails, none of it lands. Everything runs between one BEGIN and COMMIT.
• Some statements cannot run inside a transaction. Runs in 2 steps, each committed before the next: if one fails, the steps before it stay applied. This happens when a statement must run outside a transaction, as CREATE INDEX CONCURRENTLY does. If the second step fails, the column from the first is still there.

When it lands, a message says Applied 2 structure changes to orders, the bar goes, and the Structure tab shows the new column and index.

On a production connection, applying asks first. See Work on production without breaking it.

Change your mind

• In the review, click Unstage, the undo button beside a change, to drop that one.
• Click Discard changes in the bar, or Discard all changes in the review, to drop them all. Alcuin asks first. Nothing has run.

Staged structure changes survive a quit. They come back marked restored from your last session. If the table changed in the meantime so that they no longer fit, the bar says why, and you discard them before staging again.

To keep the change as a migration file instead of, or as well as, applying it, use Copy as migration or Save as SQL file… in the review.

What success looks like

The Structure tab lists the new column under Columns and the new index under Indexes, and the bar is gone.

If it didn’t work

• Another transaction holds a lock these changes need, so nothing was changed. Another session, often a query tab with a transaction open, is using the table. Finish that transaction, then apply again. When the changes run in several steps and some had already committed, it says …these changes need, so it stopped there. instead, and ends with how many steps were already applied and stay, such as (1 of 2 steps were already applied and stay).
• memo has a staged edit and a staged structure change means a row edit and a structure change on the same tab touch one column. Commit one before staging the other. See Edit, add and delete rows.
• No bar appears and the change runs at once: the database is in Sketch, where each change applies as you make it. There, a destructive change that Alcuin can undo exactly, by its inverse or by a checkpoint it takes first, doesn’t ask before it runs. One it can’t undo exactly waits in a toast, … waits for a yes, with Apply it and Discard.
• No Add column button means the object is a view or a materialized view, not a table.
• Apply this once? when you apply means the connection is read-only. Choose Apply once, or Unlock connection. See ALC-3001: Connection is read-only.
• If Postgres refuses a statement, such as one naming a type it doesn’t know, its error shows in the review or the bar. In one transaction, nothing is applied.