Save this content to: .claude/skills/pgsql-enums-postgres-migrations/SKILL.md

# PostgreSQL ENUM migrations (adonyarik/pgsql-enums)

## When to use this skill

Use it when a migration creates, changes or drops a native PostgreSQL ENUM type, or when app code or tests touch a column of such a type. When this skill leaves a case open, or you are unsure which approach fits, read the source in `vendor/adonyarik/pgsql-enums/src/`: `PostgresEnumManager.php` holds the SQL and the checks, and every method has a phpDoc block.

## Ground rules

- Package discovery registers the macros. Nothing to publish or configure.
- The DB macros run on the **default** connection; the Blueprint macros run on the connection of their `Schema::connection(...)` call. Either must be PostgreSQL: on SQLite or MySQL the macros throw `PostgresEnumException` (`unsupportedDriver`), so the app's tests need a PostgreSQL database.
- Laravel's own `$table->enum()` makes a `varchar` with a `CHECK` constraint on PostgreSQL, not a native type. Use the macros below for a native type, and keep a table on whichever approach it already uses.
- Type names are not prefixed. An unqualified name resolves through the `search_path`; `schema.name` names a schema explicitly, for types and tables alike. Name types `{singular_table}_{column}`, such as `post_status`, at most 63 bytes.
- The Blueprint macros add the connection's table prefix. The DB macros take the table name as it is in the database, with the prefix.
- Values are strings. Their order is the type's sort order, so `orderBy('status')` sorts by it, not alphabetically. Ints, such as `Priority::values()` of an int-backed PHP enum, are stored as strings. A list must not be empty or repeat a value, and each value is at most 63 bytes.
- `pgEnumRedefine*` take the **complete** new list of values, not the ones to add or remove.
- The package checks what it can before anything changes and throws `Adonyarik\PgsqlEnums\Exceptions\PostgresEnumException`. What PostgreSQL itself refuses throws `Illuminate\Database\QueryException`, and a change of several steps rolls back as a whole.
- The macros act on enum types only: a name that resolves to a domain, a table's row type or a base type throws `notAnEnum`.
- None of the macros has an "if exists" variant. Guard with `app(PostgresEnumManager::class)->enumTypeExists('post_status')` when needed.

## API

```php
use Illuminate\Support\Facades\DB;

DB::pgEnumCreate(string $type, array $values): void
DB::pgEnumConvertColumn(string $table, string $column, string $type): void
DB::pgEnumRedefine(string $type, array $values): void
DB::pgEnumRedefineWithDefault(string $table, string $column, string $type, array $values, string $default): void
DB::pgEnumDrop(string $type): void

// Inside Schema::create() / Schema::table()
$table->pgEnumColumn(string $column, string $type, array $options = []): ColumnDefinition
$table->pgEnumColumnNew(string $column, string $type, array $values, array $options = []): ColumnDefinition
$table->pgEnumDefault(string $column, string $type, string $value): void
```

| Macro | Behavior |
| ----- | -------- |
| `pgEnumCreate` | Creates the type in the current schema, or in the schema of `schema.name`. Throws if a type or table of that name exists there |
| `pgEnumConvertColumn` | Converts a column, usually a string one, to an existing type. Lists the values outside the type and throws before changing anything. NULLs are fine. Fails on a default PostgreSQL cannot cast, such as a `varchar` default |
| `pgEnumRedefine` | Replaces the values and recasts every column of the type in every schema, partitions and child tables included, keeping their defaults and the type's owner, comment and privileges, in one transaction. New values are usable right away in the same transaction. Rewrites each table that uses the type and locks it, reads included, until the transaction ends. Does nothing when the values do not change |
| `pgEnumRedefineWithDefault` | Same for a type that only this column uses (its partitions and child tables follow it), then sets the column's default. Throws when the column does not use the type or another column does |
| `pgEnumDrop` | Drops a type nothing uses. Throws, naming them, while columns or other objects still use it |
| `pgEnumColumn` | Adds a column of an existing enum type. Throws when the type is missing or not an enum |
| `pgEnumColumnNew` | Creates the type at once, when the closure runs, then adds the column |
| `pgEnumDefault` | Checks, when called, that the column uses the type (from its definition in the same Blueprint, else from the database) and that the value belongs to it, then sets the default with a separate `ALTER TABLE` after the table statement |

The column macros return the column definition, so `->nullable()`, `->default()`, `->comment()` and other modifiers chain as usual. `$options` sets the same attributes, such as `['nullable' => true]`, but not `type` or `name`.

## Recipes

Each recipe shows `up()` and `down()`. Imports: `Illuminate\Database\Schema\Blueprint`, `Illuminate\Support\Facades\DB`, `Illuminate\Support\Facades\Schema`.

### New table with an enum column

```php
public function up(): void
{
    Schema::create('posts', function (Blueprint $table) {
        $table->id();
        $table->pgEnumColumnNew('status', 'post_status', ['draft', 'published', 'archived']);
        $table->pgEnumDefault('status', 'post_status', 'draft');
        $table->timestamps();
    });
}

public function down(): void
{
    Schema::dropIfExists('posts');
    DB::pgEnumDrop('post_status'); // after the table: a type in use cannot be dropped
}
```

Call `pgEnumDefault` after `pgEnumColumnNew`: it checks the value against the type as soon as it is called.

### Another table using an existing type

```php
Schema::create('pages', function (Blueprint $table) {
    $table->id();
    $table->pgEnumColumn('status', 'post_status')->nullable();
});
// down(): Schema::dropIfExists('pages'); the type belongs to the migration that created it
```

### Enum column on an existing table

```php
public function up(): void
{
    Schema::table('posts', function (Blueprint $table) {
        $table->pgEnumColumnNew('visibility', 'post_visibility', ['public', 'private'])->default('public');
    });
}

public function down(): void
{
    Schema::table('posts', fn (Blueprint $table) => $table->dropColumn('visibility'));
    DB::pgEnumDrop('post_visibility');
}
```

On a table that has rows, a NOT NULL column needs the inline `->default()`. `pgEnumDefault` runs after `ADD COLUMN`, too late for the existing rows. Otherwise make the column `->nullable()`.

### Convert a string column

```php
public function up(): void
{
    DB::pgEnumCreate('post_status', ['draft', 'published', 'archived']);
    // Only when the column has a default: a varchar default blocks the conversion
    DB::statement('alter table posts alter column status drop default');
    DB::pgEnumConvertColumn('posts', 'status', 'post_status');
    Schema::table('posts', fn (Blueprint $table) => $table->pgEnumDefault('status', 'post_status', 'draft'));
}

public function down(): void
{
    // change() restates every modifier the column keeps (nullable, default)
    Schema::table('posts', fn (Blueprint $table) => $table->string('status')->default('draft')->change());
    DB::pgEnumDrop('post_status');
}
```

Prefer `pgEnumConvertColumn` to `->change()`: it checks the values first. A plain `$table->pgEnumColumn(...)->change()` fails, since PostgreSQL cannot cast the column without `USING`; Laravel 13.23+ accepts `->using('"status"::"post_status"')->change()`, without the check. When rows hold values outside the type, `pgEnumConvertColumn` throws `columnHasInvalidValues` naming them. Fix them first, with `DB::table('posts')->whereNotIn('status', [...])->update([...])`.

### Add a value

```php
public function up(): void
{
    DB::pgEnumRedefine('post_status', ['draft', 'published', 'archived', 'deleted']);
}

public function down(): void
{
    DB::table('posts')->where('status', 'deleted')->update(['status' => 'archived']);
    DB::pgEnumRedefine('post_status', ['draft', 'published', 'archived']);
}
```

`pgEnumRedefine` rewrites every table that uses the type and blocks even reads of it until the migration commits. For a very large table, `DB::statement("alter type post_status add value 'deleted' after 'archived'")` avoids the rewrite. The new value cannot be used before the migration's transaction commits, though. Only `pgEnumRedefine` can remove the value later.

### Remove a value

```php
public function up(): void
{
    // Move the rows first, in every table that uses the type, or the redefine rolls back
    DB::table('posts')->where('status', 'archived')->update(['status' => 'draft']);
    DB::pgEnumRedefine('post_status', ['draft', 'published']);
}

public function down(): void
{
    DB::pgEnumRedefine('post_status', ['draft', 'published', 'archived']);
}
```

When a column default is the removed value, `pgEnumRedefine` rolls back too. For a type that one column uses, `DB::pgEnumRedefineWithDefault('posts', 'status', 'post_status', ['published', 'archived'], 'published')` replaces the default in the same step. For a shared type, first point each default at a value that stays, with `$table->pgEnumDefault()` in `Schema::table()`.

### Rename a value

The package has no macro for it. PostgreSQL renames in place, without a table rewrite, and rows and defaults follow:

```php
public function up(): void
{
    DB::statement("alter type post_status rename value 'draft' to 'pending'");
}

public function down(): void
{
    DB::statement("alter type post_status rename value 'pending' to 'draft'");
}
```

### Reorder values

`DB::pgEnumRedefine('post_status', ['archived', 'published', 'draft'])` with the same values in the new order. `down()` restores the old order.

### Drop an enum column and its type

```php
public function up(): void
{
    Schema::table('posts', fn (Blueprint $table) => $table->dropColumn('status'));
    DB::pgEnumDrop('post_status');
}

public function down(): void
{
    Schema::table('posts', function (Blueprint $table) {
        $table->pgEnumColumnNew('status', 'post_status', ['draft', 'published'])->nullable();
    });
}
```

## PHP enums and the database type

- Keep the PHP enum and the type in step. A case added to or removed from a backed enum mapped to a type needs a migration that redefines the type.
- `PostStatus::values()` from the `EnumHelpers` trait is fine in the migration that creates the type. A migration that changes values lists the old and new values literally: the enum only knows its current cases, and `down()` must restore the old list.
- Cast the column on the model: `protected function casts(): array { return ['status' => PostStatus::class]; }`. Eloquent then reads and writes enum instances, and `where('status', PostStatus::Published)` works.
- A string outside the type in a query, such as `where('status', 'bogus')`, throws `QueryException` ("invalid input value for enum") instead of returning no rows. Validate request input, with `Rule::enum(PostStatus::class)` or `Rule::in(PostStatus::values())`, before it reaches a query.

## Tests and fresh databases

- `migrate:fresh` and `db:wipe` drop tables but keep PostgreSQL types. The next `pgEnumCreate` then throws `typeAlreadyExists`. Run `php artisan migrate:fresh --drop-types`.
- `RefreshDatabase` and `DatabaseMigrations` run `migrate:fresh`. Add `--drop-types` to it from the base test case:

```php
abstract class TestCase extends BaseTestCase
{
    protected $dropTypes = true;
}
```

- The test connection must be PostgreSQL (`DB_CONNECTION=pgsql` in `phpunit.xml`). An in-memory SQLite database cannot run these migrations.
- The macros resolve `Adonyarik\PgsqlEnums\PostgresEnumManager` from the container on every call, so `$this->mock(PostgresEnumManager::class, ...)` or `$this->app->instance(...)` replaces them in a test. The Blueprint macros call `onConnection()` on it first: a mock returns itself there (`->shouldReceive('onConnection')->andReturnSelf()`).

## Errors

`PostgresEnumException` factories, all thrown before anything changes. Each message names the type, the table and the values involved, and the exception keeps them in its `type`, `table`, `column` and `values` properties.

| Factory | Thrown when |
| ------- | ----------- |
| `typeAlreadyExists` | Creating a type whose name a type or table takes in the target schema (`pgEnumCreate`, `pgEnumColumnNew`) |
| `typeMissing` | Any operation on a type that does not exist |
| `notAnEnum` | The name resolves to a domain, a row type, a base type or another type that is not an enum |
| `schemaMissing`, `tableMissing`, `columnMissing` | The schema of `schema.name`, the table, or the column to convert does not exist |
| `invalidName` | A name that is empty, has more than one dot or is longer than 63 bytes |
| `emptyValues`, `invalidValue`, `duplicateValues` | An empty list; a value that is not a string or an int, longer than 63 bytes or not UTF-8; a repeated value |
| `columnHasInvalidValues` | `pgEnumConvertColumn` on a column that holds values outside the type |
| `valueNotAllowed` | A default outside the type's values (`pgEnumDefault`, `setColumnDefault`) |
| `defaultNotInValues` | `pgEnumRedefineWithDefault` with a default that is not one of the new values |
| `typeStillReferenced` | `pgEnumDrop` while columns or other objects use the type |
| `columnDoesNotUseType` | `pgEnumDefault` or `pgEnumRedefineWithDefault` on a column of another type, or a missing column |
| `typeSharedWithOtherColumns` | `pgEnumRedefineWithDefault` on a type that other columns use |
| `unsupportedDependents` | `pgEnumRedefine*` on a type that objects PostgreSQL cannot recast depend on (see Limits) |
| `unsupportedDriver` | The connection is not PostgreSQL |
| `reservedColumnOptions` | Column `$options` that set `type` or `name` |

## Limits

- `pgEnumRedefine*` throw `unsupportedDependents` before changing anything when the type is used by something PostgreSQL cannot move to a new type: array columns, domains, composite types, views, functions and foreign tables; partial indexes and constraints whose expressions name a value (`where status = 'active'`); views, triggers, policies and publications that use a column of the type, and columns generated from one; generated and partition key columns of the type; foreign keys between two tables of the type. Drop them before the change and create them again after. Plain and unique indexes are fine.
- `php artisan migrate --pretend` prints the macros' SQL. The checks that depend on the database state are skipped there, since the earlier statements were only pretended too.
- `pgEnumColumnNew` creates the type before the table statement. Inside a migration it rolls back with a failed table; outside a transaction a failed `create table` leaves the type behind.

## PostgresEnumManager

The service behind the macros, bound as a singleton. Resolve it with `app(PostgresEnumManager::class)` when code outside a migration needs the checks:

| Method | Returns / does |
| ------ | -------------- |
| `enumTypeExists(string $type): bool` | Whether the name resolves to an enum type |
| `typeExists(string $type): bool` | Whether the name resolves to a type of any kind |
| `onConnection(Connection $connection): static` | A copy that works on that connection instead of the default one |
| `allowedValues(string $type): array` | The values in sort order; `[]` for a missing or non-enum type |
| `columnsUsingType(string $type): array` | Rows with `table_schema`, `table_name`, `column_name`, `column_default`, `inherited`, `generated` |
| `setColumnDefault(string $table, string $column, string $type, string $value): void` | Checks that the column uses the type and the value belongs to it, then sets the default. The table name is taken as is, without the prefix |
| `createType`, `dropType`, `convertColumn`, `redefineValues`, `redefineValuesWithDefault` | What `pgEnumCreate`, `pgEnumDrop`, `pgEnumConvertColumn`, `pgEnumRedefine` and `pgEnumRedefineWithDefault` call, with the same arguments |