Skip to content

Instantly share code, notes, and snippets.

@tjdevries

tjdevries/test.t

Created February 25, 2026 14:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tjdevries/ce8d16ba1e48d79ff0bc6ab1f11cd5d3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tjdevries/ce8d16ba1e48d79ff0bc6ab1f11cd5d3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
test.t
PgDiff schema diff — all cases: previous schema, current schema, and migration SQL.
$ test-cram-pg-diff
=== empty-to-empty ===
previous:
(empty)
previous (json):
[]
current:
(empty)
current (json):
[]
diff:
(no ops)
=== empty-to-table ===
previous:
(empty)
previous (json):
[]
current:
CREATE TABLE users (id INTEGER)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- create table users (1 columns)
CREATE TABLE users (id INTEGER)
=== table-to-empty ===
previous:
CREATE TABLE users (id INTEGER)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
(empty)
current (json):
[]
diff:
-- drop table users
DROP TABLE IF EXISTS users CASCADE
=== same-table ===
previous:
CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": [
"primary_key",
"not_null"
]
},
{
"name": "name",
"type": "text",
"constraints": [
"not_null"
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": [
"primary_key",
"not_null"
]
},
{
"name": "name",
"type": "text",
"constraints": [
"not_null"
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
(no ops)
=== multiple-new-tables ===
previous:
(empty)
previous (json):
[]
current:
CREATE TABLE users (id INTEGER)
CREATE TABLE posts (id INTEGER)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
},
{
"name": "posts",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- create table users (1 columns)
CREATE TABLE users (id INTEGER)
-- create table posts (1 columns)
CREATE TABLE posts (id INTEGER)
=== multiple-dropped-tables ===
previous:
CREATE TABLE users (id INTEGER)
CREATE TABLE posts (id INTEGER)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
},
{
"name": "posts",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
(empty)
current (json):
[]
diff:
-- drop table users
DROP TABLE IF EXISTS users CASCADE
-- drop table posts
DROP TABLE IF EXISTS posts CASCADE
=== add-column ===
previous:
CREATE TABLE users (id INTEGER)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (id INTEGER, email TEXT NOT NULL)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "email",
"type": "text",
"constraints": [
"not_null"
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- add column users.email (text)
ALTER TABLE users ADD COLUMN email TEXT NOT NULL
=== drop-column ===
previous:
CREATE TABLE users (id INTEGER, email TEXT)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (id INTEGER)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop column users.email
ALTER TABLE users DROP COLUMN email
=== change-column-type ===
previous:
CREATE TABLE users (score INTEGER)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "score",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (score DOUBLE PRECISION)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "score",
"type": "double_precision",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- alter column users.score type integer → double_precision
ALTER TABLE users ALTER COLUMN score TYPE DOUBLE PRECISION
=== add-not-null ===
previous:
CREATE TABLE users (name TEXT)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "name",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (name TEXT NOT NULL)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "name",
"type": "text",
"constraints": [
"not_null"
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- set not null users.name
ALTER TABLE users ALTER COLUMN name SET NOT NULL
=== remove-not-null ===
previous:
CREATE TABLE users (name TEXT NOT NULL)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "name",
"type": "text",
"constraints": [
"not_null"
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (name TEXT)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "name",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop not null users.name
ALTER TABLE users ALTER COLUMN name DROP NOT NULL
=== add-default ===
previous:
CREATE TABLE users (active BOOLEAN)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "active",
"type": "boolean",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (active BOOLEAN DEFAULT true)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "active",
"type": "boolean",
"constraints": [
{
"default": "true"
}
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- add default users.active = true
ALTER TABLE users ALTER COLUMN active SET DEFAULT true
=== remove-default ===
previous:
CREATE TABLE users (active BOOLEAN DEFAULT true)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "active",
"type": "boolean",
"constraints": [
{
"default": "true"
}
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (active BOOLEAN)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "active",
"type": "boolean",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop default users.active
ALTER TABLE users ALTER COLUMN active DROP DEFAULT
=== change-default ===
previous:
CREATE TABLE users (score INTEGER DEFAULT 0)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "score",
"type": "integer",
"constraints": [
{
"default": "0"
}
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (score INTEGER DEFAULT 100)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "score",
"type": "integer",
"constraints": [
{
"default": "100"
}
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- add default users.score = 100
ALTER TABLE users ALTER COLUMN score SET DEFAULT 100
=== add-unique ===
previous:
CREATE TABLE users (email TEXT)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (email TEXT UNIQUE)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": [
"unique"
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- add unique constraint on users.email
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email)
=== remove-unique ===
previous:
CREATE TABLE users (email TEXT UNIQUE)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": [
"unique"
]
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (email TEXT)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop unique constraint on users.email
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_unique
=== multiple-column-changes ===
previous:
CREATE TABLE users (id INTEGER, name TEXT, old_field TEXT)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "name",
"type": "text",
"constraints": []
},
{
"name": "old_field",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (id INTEGER, name TEXT NOT NULL, email TEXT)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "name",
"type": "text",
"constraints": [
"not_null"
]
},
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop column users.old_field
ALTER TABLE users DROP COLUMN old_field
-- set not null users.name
ALTER TABLE users ALTER COLUMN name SET NOT NULL
-- add column users.email (text)
ALTER TABLE users ADD COLUMN email TEXT
=== add-table-constraint ===
previous:
CREATE TABLE users (id INTEGER)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (id INTEGER, CHECK (id > 0))
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [
{
"type": "check",
"expr": "id > 0"
}
],
"indexes": [],
"raw_sql": []
}
]
diff:
-- add constraint on users: check (id > 0)
ALTER TABLE users ADD CHECK (id > 0)
=== drop-table-constraint ===
previous:
CREATE TABLE users (id INTEGER, CHECK (id > 0))
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [
{
"type": "check",
"expr": "id > 0"
}
],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (id INTEGER)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop constraint users.users_check
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_check
=== add-foreign-key ===
previous:
CREATE TABLE posts (id INTEGER, user_id INTEGER)
previous (json):
[
{
"name": "posts",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "user_id",
"type": "integer",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE posts (id INTEGER, user_id INTEGER, CONSTRAINT posts_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE NO ACTION)
current (json):
[
{
"name": "posts",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "user_id",
"type": "integer",
"constraints": []
}
],
"constraints": [
{
"type": "foreign_key",
"foreign_key": {
"columns": [
"user_id"
],
"references_table": "users",
"references_columns": [
"id"
],
"on_delete": "cascade",
"on_update": "no_action"
}
}
],
"indexes": [],
"raw_sql": []
}
]
diff:
-- add constraint on posts: foreign key (user_id) → users(id)
ALTER TABLE posts ADD CONSTRAINT posts_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE NO ACTION
=== add-index ===
previous:
CREATE TABLE users (email TEXT)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (email TEXT)
CREATE INDEX users_email_idx ON users (email)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [
{
"name": "users_email_idx",
"table_name": "users",
"columns": [
"email"
],
"unique": false
}
],
"raw_sql": []
}
]
diff:
-- create index users_email_idx on users (email)
CREATE INDEX users_email_idx ON users (email)
=== drop-index ===
previous:
CREATE TABLE users (email TEXT)
CREATE INDEX users_email_idx ON users (email)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [
{
"name": "users_email_idx",
"table_name": "users",
"columns": [
"email"
],
"unique": false
}
],
"raw_sql": []
}
]
current:
CREATE TABLE users (email TEXT)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop index users_email_idx
DROP INDEX IF EXISTS users_email_idx
=== change-index-uniqueness ===
previous:
CREATE TABLE users (email TEXT)
CREATE INDEX users_email_idx ON users (email)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [
{
"name": "users_email_idx",
"table_name": "users",
"columns": [
"email"
],
"unique": false
}
],
"raw_sql": []
}
]
current:
CREATE TABLE users (email TEXT)
CREATE UNIQUE INDEX users_email_idx ON users (email)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "email",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [
{
"name": "users_email_idx",
"table_name": "users",
"columns": [
"email"
],
"unique": true
}
],
"raw_sql": []
}
]
diff:
-- drop index users_email_idx
DROP INDEX IF EXISTS users_email_idx
-- create unique index users_email_idx on users (email)
CREATE UNIQUE INDEX users_email_idx ON users (email)
=== drops-before-adds ===
previous:
CREATE TABLE users (id INTEGER, old TEXT)
previous (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "old",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
current:
CREATE TABLE users (id INTEGER, new_col TEXT)
current (json):
[
{
"name": "users",
"columns": [
{
"name": "id",
"type": "integer",
"constraints": []
},
{
"name": "new_col",
"type": "text",
"constraints": []
}
],
"constraints": [],
"indexes": [],
"raw_sql": []
}
]
diff:
-- drop column users.old
ALTER TABLE users DROP COLUMN old
-- add column users.new_col (text)
ALTER TABLE users ADD COLUMN new_col TEXT
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment