Schema changes

ALTER TABLE

ALTER TABLE orders
  ADD COLUMN channel VARCHAR(16) DEFAULT 'web',
  ADD COLUMN note    VARCHAR,
  DROP COLUMN legacy_flag,
  ALTER COLUMN amount SET DATA TYPE DECIMAL(18,2);
 
SELECT id, channel, note FROM orders LIMIT 3;   -- works immediately; note is NULL, channel is 'web'

Renaming

Rename by copying. CREATE TABLE … AS SELECT infers the schema from the query; add the primary key with a CREATE TABLE first if you need it declared, then INSERT … SELECT.

CREATE TABLE orders_v2 (
  id          BIGINT PRIMARY KEY,
  customer_id VARCHAR NOT NULL,
  amount_gbp  DECIMAL(18,2) NOT NULL          -- the renamed column
);
INSERT INTO orders_v2 SELECT id, customer_id, amount FROM orders;   -- columns map by position
DROP TABLE orders;

Writes that arrive between the copy and the DROP are not carried over. Do the switch in a maintenance window, or dual-write during it.

Indexes

CREATE INDEX ON customers (email);
DROP TABLE __idx_customers_email;            -- the DROP INDEX spelling
 
CREATE UNIQUE INDEX ON memberships (org_id, user_id);
DROP TABLE __uidx_memberships_org_id_user_id;

SHOW VECTOR INDEXES lists vector indexes and their build state (building for an async build).

TRUNCATE

TRUNCATE TABLE t (or TRUNCATE t) removes every row and keeps the schema, primary key, indexes, constraints and graph bindings. It is the answer to “clear the table”, since DELETE FROM t without a WHERE is refused. TRUNCATE a, b and TRUNCATE … PARTITION (…) are refused: one table per statement, all rows.

RESTORE

Every commit to a table is kept as a version, so you can read the past and go back to it.

SELECT COUNT(*) FROM orders VERSION AS OF 42;                       -- read a version, table unchanged
RESTORE TABLE orders TO VERSION AS OF 42;                           -- go back
RESTORE TABLE orders TO TIMESTAMP AS OF '2026-09-17T10:00:00Z';    -- offset required

What RESTORE does, precisely:

• It rolls the table back to the target version and discards every write acknowledged after it, including writes that had not yet been folded into a version. They cannot come back.
• Indexes are not rolled back. Rebuild them (DROP TABLE __idx_…, CREATE INDEX …) if the restored data no longer matches.
• TIMESTAMP AS OF requires an explicit offset (…Z, …+02:00).
• History older than 168 hours may have been removed by vacuum; a version that no longer exists cannot be restored to. There is no way to lower that floor.

The Time travel & restore recipe walks through a safe restore.

Migration tools

The engine accepts what Alembic, Django migrations, Prisma Migrate and Prisma db push emit, with these consequences:

Prisma db push and prisma migrate dev complete, SQLAlchemy create_all and Alembic upgrade/downgrade complete, Django migrate completes. The Migrating from Postgres checklist has the per-ORM notes.

Reserved words

The dialect’s reserved words include a few common column names: at, end, order, table, to, user, offset. A bare at TIMESTAMP in CREATE TABLE, or WHERE at > …, is a syntax error. Double-quote the identifier everywhere it is referenced ("at"), or pick an unreserved name such as occurred_at.