Setting the file. One moment.
Subchapter 63.22
references/mysql-migrations/ddl-auto-increment.mdMarkdown5 KBView on GitHub
Part of MySQL to DSQL DDL Migration. See Common Verify & Swap Pattern for the shared migration end-pattern.
MySQL syntax:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);DSQL provides three options for replacing MySQL’s AUTO_INCREMENT. Choose based on your workload requirements. See Choosing Identifier Types in the scaling guide for detailed guidance.
When choosing integer auto-increment, ALWAYS use GENERATED AS IDENTITY (not SERIAL, which DSQL does not support). UUIDs (Option 1) remain the recommended default.
UUIDs are the recommended default because they avoid coordination and scale well for distributed writes.
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255)
);DSQL:
gen_random_uuid()is built-in; do NOT runCREATE EXTENSION pgcrypto. DSQL ships PostgreSQL 16’s coregen_random_uuid(), so the extension is unnecessary ANDCREATE EXTENSIONis rejected by DSQL (ERROR: unsupported statement: CreateExtension). Otherpgcryptofunctions (crypt(),digest(),hmac(), etc.) are unavailable — implement those at the application layer.
Use GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY when compact, human-readable integer IDs
are needed.
DSQL:
CACHEis mandatory. DSQL has no implicit default and rejects identity columns declared without it:ERROR: identity column is not supported without an explicit cache size. please define CACHE greater than or equal to 65536 or equal to 1. A migration tool replaying a vanilla PostgreSQL dump (whereCACHEdefaults to 1) will fail at the firstIDENTITYcolumn. Always include(CACHE 1)for strict ordering or(CACHE 65536)(or higher) for high-throughput workloads — see scaling-guide.md.
-- GENERATED ALWAYS: DSQL always generates the value; explicit inserts rejected unless OVERRIDING SYSTEM VALUE
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 65536) PRIMARY KEY,
name VARCHAR(255)
);
-- GENERATED BY DEFAULT: DSQL generates a value unless an explicit value is provided (closer to MySQL AUTO_INCREMENT behavior)
CREATE TABLE users (
id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
name VARCHAR(255)
);REQUIRED: Specify CACHE explicitly. Supported values are 1 or >= 65536.
Use a standalone sequence when multiple tables share a counter or when you need nextval/setval control.
-- Create the sequence (CACHE MUST be 1 or >= 65536)
CREATE SEQUENCE users_id_seq CACHE 65536 START 1;
-- Create table using the sequence
CREATE TABLE users (
id BIGINT PRIMARY KEY DEFAULT nextval('users_id_seq'),
name VARCHAR(255)
);CREATE TABLE users_new (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
legacy_id INTEGER, -- Preserve original AUTO_INCREMENT ID for reference
name VARCHAR(255)
);
INSERT INTO users_new (id, legacy_id, name)
SELECT gen_random_uuid(), id, name
FROM users;If other tables reference the old integer ID, update those references to use the new UUID or the legacy_id column.
-- Use GENERATED BY DEFAULT to allow explicit ID values during migration
CREATE TABLE users_new (
id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
name VARCHAR(255)
);
-- Migrate with original integer IDs preserved
INSERT INTO users_new (id, name)
SELECT id, name
FROM users;
-- Set the identity sequence to continue after the max existing ID
-- Get the max ID first:
SELECT MAX(id) as max_id FROM users_new;
-- Then reset the sequence (find the sequence name via:
-- SELECT pg_get_serial_sequence('users_new', 'id');):
SELECT setval('users_new_id_seq', (SELECT MAX(id) FROM users_new));Verify and swap (see Common Pattern)