How do I fix syntax error?

Drop the RESTART IDENTITY clause. CASCADE is supported. If you do have a real sequence to reset, do it separately. Never let a truncate failure pass silently — a load onto stale rows looks like a successful migration until somebody counts.

CockroachDB · error reference

CockroachDB: at or near "restart" syntax error on TRUNCATE

at or near "restart": syntax error

TRUNCATE ... RESTART IDENTITY CASCADE is valid Postgres and a syntax error on CockroachDB. The difference, and what to use instead.

What the database is telling you

CockroachDB supports TRUNCATE with CASCADE but not the RESTART IDENTITY clause. In the usual case there is nothing to restart: CockroachDB's default for generated primary keys is unique_rowid(), which is not a sequence.

Why it shows up in a migration

This appears when a loader clears the target before a full copy. If the truncate is wrapped in a try/except — and it very often is, because 'the table might not exist yet' is a normal condition — the syntax error is swallowed and the tables are simply not cleared. The migration then loads on top of the previous run's rows, and you find out from a row-count check, if you have one.

Common causes

Postgres truncate syntax used against CockroachDB.
A tool keyed on the wire protocol rather than the actual engine.
Truncate errors caught and logged at debug level, hiding the failure.

How to fix it

Drop the RESTART IDENTITY clause. CASCADE is supported.

-- Postgres
TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

-- CockroachDB
TRUNCATE TABLE orders CASCADE;

2
If you do have a real sequence to reset, do it separately.
```
ALTER SEQUENCE orders_id_seq RESTART WITH 1;
```
3
Never let a truncate failure pass silently — a load onto stale rows looks like a successful migration until somebody counts.

What DBShifts does about it

The truncate phase selects syntax by dialect and omits RESTART IDENTITY for CockroachDB specifically. The bug this page documents was real and ours: keyed on the raw engine name, CockroachDB and YugabyteDB matched no branch at all, so a re-run silently skipped the truncate.

Bulk transfer engine

Migrating between engines?

DBShifts converts the schema, moves the data, and then proves the two sides match with a type-aware checksum per table — not just a row count. Errors like this one are handled on the way, and the ones that cannot be handled are reported rather than logged and forgotten.

Start free See the migration paths

Related errors

CockroachDB

CockroachDB: session_replication_role is not supported

The standard Postgres trick for loading data with foreign keys disabled does not exist on CockroachDB. What to do instead, and why the usual workaround is unnecessary here.

PostgreSQL

asyncpg: a sized iterable container expected (got type 'str')

asyncpg rejected a string bound to an array column. Nearly always a MySQL SET, or a comma-joined list that was never converted to a Python list.

← All database errors