### Example .env File Setup

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/dotenv.mdx

Create a .env file in your project directory to define database connection settings and optional parameters like SSL mode and application name.

```bash
# Database connection settings
PGHOST=localhost
PGPORT=5432
PGDATABASE=myapp
PGUSER=postgres
PGPASSWORD=secretpassword

# Optional: SSL mode (disable, allow, prefer, require, verify-ca, verify-full)
PGSSLMODE=prefer

# Optional: Custom application name
PGAPPNAME=pgschema
```

--------------------------------

### Install and Import `pgschema/ir` Package

Source: https://github.com/pgplex/pgschema/blob/main/ir/README.md

Install the `pgschema` library using `go get` and import the `ir` package for use in your Go project.

```bash
go get github.com/pgplex/pgschema
```

```go
import "github.com/pgplex/pgschema/ir"
```

--------------------------------

### Start Local Development Server

Source: https://github.com/pgplex/pgschema/blob/main/docs/README.md

Run the Mintlify development server from the root of your documentation directory. This command will start a local preview server.

```bash
mint dev
```

--------------------------------

### Install pgschema with Nix

Source: https://github.com/pgplex/pgschema/blob/main/README.md

Install and run pgschema using Nix. Pass arguments to the run command using '--'.

```bash
nix build
nix run
```

```bash
nix run . -- plan
```

--------------------------------

### Install pgschema Pre-built Binary on Linux (ARM64)

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Download and install the pre-built binary for Linux ARM64 systems. Replace `v1.0.0` with the latest release version.

```bash
# Download the binary
curl -L https://github.com/pgplex/pgschema/releases/download/v1.0.0/pgschema-linux-arm64 -o pgschema

# Make it executable
chmod +x pgschema

# Move to PATH
sudo mv pgschema /usr/local/bin/

# Verify installation
pgschema --help
```

--------------------------------

### Verify pgschema Installation

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

After installation, run this command to view the help message and confirm the version of pgschema. This helps verify that the installation was successful and the binary is in your PATH.

```bash
pgschema --help
```

--------------------------------

### Install Mintlify CLI

Source: https://github.com/pgplex/pgschema/blob/main/docs/README.md

Install the Mintlify CLI globally using npm. This is required to preview documentation changes locally.

```bash
npm i -g mint
```

--------------------------------

### Setup Go Environment

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/gitops.mdx

Configures the GitHub Actions runner with the specified Go version.

```yaml
- uses: actions/setup-go@v5
  with:
    go-version: '1.24'

```

--------------------------------

### Install pgschema using Go

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Install the latest version of pgschema directly using the Go toolchain if you have Go 1.24.0 or later. This command installs the binary to your Go bin directory.

```bash
go install github.com/pgplex/pgschema@latest
```

--------------------------------

### Install pgschema Pre-built Binary on Linux (AMD64)

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Download and install the pre-built binary for Linux AMD64 systems. Replace `v1.0.0` with the latest release version.

```bash
# Download the binary
curl -L https://github.com/pgplex/pgschema/releases/download/v1.0.0/pgschema-linux-amd64 -o pgschema

# Make it executable
chmod +x pgschema

# Move to PATH
sudo mv pgschema /usr/local/bin/

# Verify installation
pgschema --help
```

--------------------------------

### Install pgschema with Pre-built Binary on macOS

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Download and install the pre-built binary for macOS. Remember to replace `v1.0.0` with the latest release version.

```bash
# Download the binary
curl -L https://github.com/pgplex/pgschema/releases/download/v1.0.0/pgschema-1.0.0-darwin-arm64 -o pgschema

# Make it executable
chmod +x pgschema

# Move to PATH
sudo mv pgschema /usr/local/bin/

# Verify installation
pgschema --help
```

--------------------------------

### Install pgschema CLI

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/gitops.mdx

Installs the pgschema command-line interface tool using Go modules.

```yaml
- name: Install pgschema
  run: go install github.com/pgplex/pgschema@latest

```

--------------------------------

### Fingerprint Validation Example

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/plan-review-apply.mdx

Example demonstrating fingerprint validation during the 'apply' phase and potential outcomes. If fingerprints mismatch, apply is aborted.

```bash
# Generate plan (captures current state fingerprint)
pgschema plan --host prod.db.com --db myapp --user postgres --file schema.sql --output-json plan.json

# Time passes... other changes might occur...

# Apply checks fingerprint before proceeding
pgschema apply --host prod.db.com --db myapp --user postgres --plan plan.json
# ✅ Success: Database state matches plan expectations
# ❌ Error: schema fingerprint mismatch detected - the database schema has changed since the plan was generated.
#
#          schema fingerprint mismatch - expected: 965b1131737c955e, actual: abc123456789abcd
```

--------------------------------

### pgschema Workflow Example

Source: https://github.com/pgplex/pgschema/blob/main/README.md

This illustrates the basic command-line workflow for using pgschema: dumping the schema, editing the schema file, planning the migration, and applying the changes.

```bash
pgschema dump → edit schema.sql → pgschema plan → pgschema apply
```

--------------------------------

### Example Migration Plan Output

Source: https://github.com/pgplex/pgschema/blob/main/docs/quickstart.mdx

The human-readable output of the plan command shows a summary of changes and the exact DDL statements that will be executed.

```sql
Plan: 2 to add, 1 to modify.

Summary by type:
  tables: 1 to add, 1 to modify
  indexes: 1 to add

Tables:
  + public.posts
  ~ public.users
    + column name
    + column updated_at

Indexes:
  + public.idx_users_name

Transaction: true

DDL to be executed:
--------------------------------------------------
ALTER TABLE users 
ADD COLUMN name VARCHAR(100);

ALTER TABLE users 
ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();

CREATE INDEX idx_users_name ON users (name);

CREATE TABLE posts (
    id SERIAL NOT NULL,
    user_id integer,
    title VARCHAR(200) NOT NULL,
    content text,
    created_at timestamp DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id)
);
```

--------------------------------

### Install PostgreSQL Extensions in Plan Database

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

Before running `pgschema plan` or `apply` with an external database that uses extensions, install the required extensions in the plan database using `psql`.

```sql
-- In your plan database, install required extensions
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
```

```bash
# Install extensions in plan database
psql -h localhost -U postgres -d pgschema_plan -c "CREATE EXTENSION IF NOT EXISTS hstore;"
```

--------------------------------

### Example SQL Dump Output

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/dump.mdx

This is an example of the SQL output generated by the pgschema dump command, including table and index definitions.

```sql
--
-- pgschema database dump
--

-- Dumped from database version PostgreSQL 17.5
-- Dumped by pgschema version 1.0.0


--
-- Name: users; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--
-- Name: idx_users_email; Type: INDEX; Schema: -; Owner: -
--

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

```

--------------------------------

### Example Output of Schema Plan

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/apply.mdx

Illustrates the detailed output generated by `pgschema apply` in file mode, showing changes to be made and prompting for user confirmation.

```text
Plan: 2 to add, 1 to modify.

Summary by type:
  tables: 1 to add, 1 to modify
  indexes: 1 to add

Tables:
  + posts
  ~ users
    + email (column)

Indexes:
  + idx_users_email

Transaction: true

DDL to be executed:
--------------------------------------------------

CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    user_id INTEGER REFERENCES users(id)
);

ALTER TABLE users ADD COLUMN email VARCHAR(255) UNIQUE;

CREATE INDEX idx_users_email ON users(email);

Do you want to apply these changes? (yes/no): 
```

--------------------------------

### Nested SQL Includes Example

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Demonstrates how to use the \i directive for nested includes in SQL schema files.

```sql
-- main.sql
\i core/init.sql
\i modules/auth/auth.sql
\i modules/reporting/reporting.sql

-- core/init.sql
\i tables/users.sql
\i tables/products.sql
\i functions/utilities.sql

-- modules/auth/auth.sql
\i tables.sql
\i functions.sql
```

--------------------------------

### Example .pgschemaignore File Configuration

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/ignore.mdx

This TOML file demonstrates how to configure ignore patterns for various database object types, including tables, views, functions, and privileges.

```toml
[tables]
patterns = ["temp_*", "test_*", "!test_core_*"]

[views]
patterns = ["debug_*", "*_view_tmp", "analytics_*"]

[functions]
patterns = ["fn_test_*", "fn_debug_*"]

[procedures]
patterns = ["sp_temp_*", "sp_legacy_*"]

[aggregates]
patterns = ["agg_test_*", "agg_debug_*"]

[types]
patterns = ["type_test_*"]

[sequences]
patterns = ["seq_temp_*", "seq_debug_*"]

[indexes]
patterns = ["idx_temp_*", "manual_*"]

[constraints]
patterns = ["fk_legacy_*"]

[triggers]
patterns = ["_vectorizer_src_trg_*"]

[privileges]
patterns = ["deploy_bot", "admin_*"]

[default_privileges]
patterns = ["deploy_bot"]
```

--------------------------------

### Create or Replace View

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/dependency/issue_444_drop_column_with_dependent_view/plan.txt

Example of creating or replacing a view. This is used to redefine views based on modified table structures.

```sql
CREATE OR REPLACE VIEW foo_v AS
 SELECT id,
    keep_me
   FROM foo;
```

```sql
CREATE OR REPLACE VIEW foo_v2 AS
 SELECT id,
    keep_me
   FROM foo_v;
```

--------------------------------

### Create Table Using Custom Domain

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/create_domain/domain_function_table_dependency/plan.txt

Creates a table named 'example' with an 'id' column of the 'custom_id' domain type. It also defines a primary key constraint on this column.

```sql
CREATE TABLE IF NOT EXISTS example (
    id custom_id,
    CONSTRAINT example_pkey PRIMARY KEY (id)
);
```

--------------------------------

### Install pgschema with Homebrew on macOS

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Use Homebrew to install pgschema on macOS. This is the recommended method. Run `brew upgrade pgschema` to update.

```bash
# Install pgschema
brew tap pgplex/pgschema
brew install pgschema

# Verify installation
pgschema --help
```

```bash
# To update to the latest version:
brew upgrade pgschema
```

--------------------------------

### Exact Pattern Examples for Tables

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/ignore.mdx

Shows how to specify exact object names in the patterns array to ignore specific tables.

```toml
[tables]
patterns = ["legacy_table", "deprecated_users", "old_audit"]
```

--------------------------------

### Negation Pattern Example for Tables

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/ignore.mdx

Demonstrates using the '!' prefix to exclude specific table patterns from broader ignore rules.

```toml
[tables]
patterns = [
  "test_*",           # Ignore all test_ tables
  "!test_core_*"      # But keep test_core_ tables
]
```

--------------------------------

### Install pgschema RPM package on RedHat/Fedora (ARM64)

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Install pgschema using the RPM package for ARM64 architecture on RedHat-based systems. Replace `v1.0.0` and `1.0.0` with the latest release version.

```bash
# Download the RPM package
curl -LO https://github.com/pgplex/pgschema/releases/download/v1.0.0/pgschema-1.0.0-1.aarch64.rpm

# Install the package
sudo rpm -i pgschema-1.0.0-1.aarch64.rpm

# Verify installation
pgschema --help
```

--------------------------------

### Apply schema plan with confirmation

Source: https://github.com/pgplex/pgschema/blob/main/README.md

Apply the generated schema plan. The `--auto-approve` flag can be used to skip the confirmation prompt. This example shows the interactive confirmation process.

```bash
# Or use --auto-approve to skip confirmation
$ PGPASSWORD=testpwd1 pgschema apply \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --plan plan.json

Plan: 1 to modify.

Summary by type:
  tables: 1 to modify

Tables:
  ~ users
    + age (column)

Transaction: true

DDL to be executed:
--------------------------------------------------

ALTER TABLE users ADD COLUMN age integer NOT NULL;

Do you want to apply these changes? (yes/no): yes

Applying changes...
Changes applied successfully!
```

--------------------------------

### Example Schema SQL Output

Source: https://github.com/pgplex/pgschema/blob/main/docs/quickstart.mdx

The output of the dump command is a clean, developer-friendly SQL file representing the schema.

```sql
-- Example output
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
```

--------------------------------

### Manus Prompt for Demo Polishing

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/demo-with-manus-and-asciinema.mdx

A detailed prompt to Manus for refining the pgschema demo, focusing on showing schema changes, adding pauses, and demonstrating the declarative approach with an ALTER TABLE example.

```text
This is too short, we still need to:

1. After dumping, show the initial schema
2. Show exactly what we change in the schema
3. Apply, then confirm with a small pause so it's visible to the viewer
4. Dump and show the schema after the change

Also, to show the declarative approach, we should add a column instead of
creating a table. Creating a table can't demonstrate the difference of the
declarative approach.

```

--------------------------------

### Wildcard Pattern Examples for Tables

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/ignore.mdx

Illustrates the use of wildcard characters (*) and negation (!) to define flexible ignore patterns for table names.

```toml
[tables]
patterns = [
  "temp_*",        # Matches: temp_backup, temp_cache, temp_session
  "*_backup",      # Matches: users_backup, orders_backup
  "test_*_data"    # Matches: test_user_data, test_order_data
]
```

--------------------------------

### Create or Replace Function

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/create_function/alter_function_attributes/plan.txt

Creates a new function or replaces an existing one. This example defines a simple function 'secure_lookup' returning text.

```sql
CREATE OR REPLACE FUNCTION secure_lookup(
    id integer
)
RETURNS text
LANGUAGE plpgsql
VOLATILE
SET search_path = pg_catalog
AS $$
BEGIN
    RETURN 'result';
END;
$$;
```

--------------------------------

### Schema Qualification Example Output

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/dump.mdx

This SQL output illustrates smart schema qualification: objects within the 'public' schema lack qualifiers, while references to objects in other schemas (e.g., 'catalog.products') are fully qualified.

```sql
-- Objects in the dumped schema have no qualifier
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),  -- Same schema, no qualifier
    product_id INTEGER REFERENCES catalog.products(id)  -- Different schema, qualified
);

```

--------------------------------

### Schema Definition Using PostgreSQL Extensions

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

Example SQL schema definition that utilizes PostgreSQL extensions like `hstore` and `postgis`. This code will work correctly when `pgschema` uses an external plan database where these extensions are installed.

```sql
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  attributes HSTORE,  -- Works because hstore extension is installed
  location GEOGRAPHY(POINT, 4326)  -- Works because postgis is installed
);
```

--------------------------------

### Plan Command with All Options

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

Configure all connection parameters for the external plan database, including host, port, database name, user, and password.

```bash
pgschema plan \
  --file schema.sql \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-port 5432 --plan-db pgschema_plan --plan-user postgres --plan-password secret
```

--------------------------------

### Apply Schema Changes with Docker

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Use this Docker command to apply schema changes defined in a plan.json file. Ensure you have Docker installed and the plan.json is in your current directory. Mount the current directory to /workspace inside the container and use --network host for localhost connections.

```bash
docker run --rm --network host \
  -v "$(pwd):/workspace" \
  -e PGPASSWORD=mypassword \
  pgplex/pgschema:latest apply \
  --host localhost \
  --port 5432 \
  --db mydb \
  --schema public \
  --user myuser \
  --plan /workspace/plan.json
```

--------------------------------

### Development Workflow: Dump, Plan, Apply

Source: https://github.com/pgplex/pgschema/blob/main/docs/coding-agent.mdx

Demonstrates a typical development workflow: dumping the production schema, modifying it locally, planning changes against a staging environment, and applying them to staging.

```bash
# 1. Dump production schema
pgschema dump --host prod --db myapp --user postgres > baseline.sql

# 2. Make changes to baseline.sql (desired state)

# 3. Test against staging
pgschema plan --host staging --db myapp --user postgres --file baseline.sql

# 4. Apply to staging  
pgschema apply --host staging --db myapp --user postgres --file baseline.sql
```

--------------------------------

### Apply Schema with External Database (File Mode)

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

The `pgschema apply` command can plan and apply schema changes in a single step. This example demonstrates its use in file mode with an external plan database.

```bash
pgschema apply \
  --file schema.sql \
  --schema public \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres \
  --auto-approve
```

--------------------------------

### Initialize Multi-File Schema Structure

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Set up a directory for your schema files and dump the existing database schema into modular files. This organizes objects by type and creates a main entry file with include directives.

```bash
# Create schema directory
mkdir -p schema

# Dump existing database schema into multiple files
pgschema dump --host localhost --db myapp --user postgres \
  --multi-file --file schema/main.sql

# Examine the generated structure
tree schema/
# schema/
# 	├── main.sql              # Main entry file with \i directives
# 	├── tables/
# 	│   ├── users.sql
# 	│   ├── orders.sql
# 	│   └── products.sql
# 	├── views/
# 	│   ├── user_orders.sql
# 	│   └── product_stats.sql
# 	├── functions/
# 	│   ├── calculate_discount.sql
# 	│   └── update_inventory.sql
# 	└── indexes/
# 	    ├── idx_users_email.sql
# 	    └── idx_orders_date.sql

# View the main.sql file
cat schema/main.sql
# \i tables/users.sql
# \i tables/orders.sql
# \i tables/products.sql
# \i views/user_orders.sql
# \i views/product_stats.sql
# \i functions/calculate_discount.sql
# \i functions/update_inventory.sql
# \i indexes/idx_users_email.sql
# \i indexes/idx_orders_date.sql

# Create CODEOWNERS file for GitHub to define team ownership
cat > .github/CODEOWNERS << 'EOF'
schema/tables/users* @user-team
schema/tables/orders* @orders-team  
schema/tables/products* @inventory-team
schema/views/user_* @user-team
schema/views/order_* @orders-team
schema/functions/*inventory* @inventory-team
EOF
```

--------------------------------

### Plan Schema with External Database

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

Use the `pgschema plan` command to generate a schema migration plan, referencing tables in other schemas. This example shows how to specify connection details for both the target and plan databases.

```bash
pgschema plan \
  --file schema.sql \
  --schema public \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres
```

--------------------------------

### Preview Staging Changes with Plan

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/local-to-production.mdx

Generate a human-readable plan and a JSON plan file for staging environment changes.

```bash
pgschema plan --host staging.db.com --db myapp --user staging \
  --file schema.sql --output-human stdout --output-json plan.json
```

--------------------------------

### Custom Organization with main.sql Directives

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Shows how to organize schema files by business domain and update the main.sql file with custom \i directives to include them in the correct order.

```sql
-- main.sql with custom organization
\i user_management/users_table.sql
\i user_management/user_sessions_table.sql  
\i inventory/products_table.sql
\i inventory/inventory_table.sql
\i orders/orders_table.sql
\i orders/order_items_table.sql
\i user_management/hash_password_function.sql
\i inventory/update_stock_procedure.sql
\i orders/calculate_total_function.sql
```

--------------------------------

### main.sql Entry Point for Schema Includes

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/pgschema-postgres-declarative-schema-migration-like-terraform.mdx

The main.sql file acts as the entry point for your schema, using PostgreSQL's \i directive to include individual component SQL files in the correct order.

```sql
-- Include custom types first (dependencies for tables)
\i types/user_status.sql
\i types/order_status.sql
\i types/address.sql

-- Include domain types (constrained base types)
\i domains/email_address.sql
\i domains/positive_integer.sql

-- Include sequences (may be used by tables)
\i sequences/global_id_seq.sql
\i sequences/order_number_seq.sql

-- Include core tables (with their constraints, indexes, and policies)
\i tables/users.sql
\i tables/orders.sql

-- Include functions and procedures
\i functions/update_timestamp.sql
\i functions/get_user_count.sql
\i procedures/cleanup_orders.sql

-- Include views (depend on tables and functions)
\i views/user_summary.sql
\i views/order_details.sql
```

--------------------------------

### Install pgschema RPM package on RedHat/Fedora (AMD64)

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Install pgschema using the RPM package for AMD64 architecture on RedHat-based systems. Replace `v1.0.0` and `1.0.0` with the latest release version.

```bash
# Download the RPM package
curl -LO https://github.com/pgplex/pgschema/releases/download/v1.0.0/pgschema-1.0.0-1.x86_64.rpm

# Install the package
sudo rpm -i pgschema-1.0.0-1.x86_64.rpm

# Verify installation
pgschema --help
```

--------------------------------

### Install pgschema DEB package on Debian/Ubuntu (ARM64)

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Install pgschema using the DEB package for ARM64 architecture on Debian-based systems. Replace `v1.0.0` and `1.0.0` with the latest release version.

```bash
# Download the DEB package
curl -LO https://github.com/pgplex/pgschema/releases/download/v1.0.0/pgschema_1.0.0_arm64.deb

# Install the package
sudo dpkg -i pgschema_1.0.0_arm64.deb

# Verify installation
pgschema --help
```

--------------------------------

### Install pgschema DEB package on Debian/Ubuntu (AMD64)

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Install pgschema using the DEB package for AMD64 architecture on Debian-based systems. Replace `v1.0.0` and `1.0.0` with the latest release version.

```bash
# Download the DEB package
curl -LO https://github.com/pgplex/pgschema/releases/download/v1.0.0/pgschema_1.0.0_amd64.deb

# Install the package
sudo dpkg -i pgschema_1.0.0_amd64.deb

# Verify installation
pgschema --help
```

--------------------------------

### Generate and Apply Schema Plan (Plan Mode)

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/apply.mdx

Demonstrates the two-step process of generating a plan JSON file using `pgschema plan` and then applying it with `pgschema apply`. This is beneficial for CI/CD and review processes.

```bash
# First, generate a plan
pgschema plan --host localhost --db myapp --user postgres --file schema.sql --output-json plan.json

# Then apply the pre-generated plan
pgschema apply --host localhost --db myapp --user postgres --plan plan.json
```

--------------------------------

### Build pgschema from Source

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Clone the pgschema repository and build the binary locally. This method requires Go 1.24.0 or later and provides the most control over the build process. The binary is placed in the current directory.

```bash
# Clone the repository
git clone https://github.com/pgplex/pgschema.git
cd pgschema

# Build the binary
go build -v -o pgschema .

# Optional: Install to system
sudo mv pgschema /usr/local/bin/
```

--------------------------------

### Create Integer Sequence with Start Value

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/create_sequence/add_sequence/plan.txt

Creates a sequence named 'int_seq' as an integer, starting from 100, with a cache size of 5. Suitable for integer generation with a specific initial offset.

```sql
CREATE SEQUENCE IF NOT EXISTS int_seq AS integer START WITH 100 CACHE 5;
```

--------------------------------

### Create or Replace Trigger

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/dependency/issue_444_drop_column_with_dependent_view/plan.txt

Defines a trigger that executes a function. This example shows an INSTEAD OF trigger for a view.

```sql
CREATE OR REPLACE TRIGGER trg_keep
    INSTEAD OF INSERT ON foo_v2
    FOR EACH ROW
    EXECUTE FUNCTION foo_v2_noop();
```

--------------------------------

### Basic Plan Mode Usage

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/apply.mdx

Executes a pre-generated migration plan from a JSON file.

```bash
pgschema apply --host localhost --db myapp --user postgres --password mypassword --plan plan.json
```

--------------------------------

### Basic Plan Generation

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan.mdx

Generate a migration plan to apply schema.sql to the target database.

```bash
pgschema plan --host localhost --db myapp --user postgres --password mypassword --file schema.sql
```

--------------------------------

### Simple Table Creation

Source: https://github.com/pgplex/pgschema/blob/main/docs/syntax/create_table.mdx

Demonstrates the creation of a simple table named 'users' with various column types, constraints, and default values. All constraints are defined at the table level with explicit names.

```sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL,
    email VARCHAR(255) NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT users_age_check CHECK (age >= 0)
);
```

--------------------------------

### Clone and Build PgSchema

Source: https://github.com/pgplex/pgschema/blob/main/README.md

Clone the PgSchema repository, navigate into the directory, tidy Go modules, and build the executable.

```bash
git clone https://github.com/pgplex/pgschema.git
cd pgschema
go mod tidy
go build -o pgschema .
```

--------------------------------

### Dump schema using .env file

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/dump.mdx

Demonstrates how to configure connection parameters using a .env file. This is the recommended method for managing sensitive information like passwords.

```bash
# Create .env file with:
# PGHOST=localhost
# PGPORT=5432
# PGDATABASE=myapp
# PGUSER=postgres
# PGPASSWORD=mypassword

pgschema dump
```

--------------------------------

### Connection using .env File

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/apply.mdx

Configures database connection parameters using a .env file, which is the recommended method for managing credentials.

```bash
# Create .env file with:
# PGHOST=localhost
# PGPORT=5432
# PGDATABASE=myapp
# PGUSER=postgres
# PGPASSWORD=mypassword

pgschema apply --file schema.sql
```

--------------------------------

### Create Get Document Count Function

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/dependency/table_to_function/plan.txt

Creates a PostgreSQL function that returns the total number of records in the 'documents' table.

```sql
CREATE OR REPLACE FUNCTION get_document_count()
RETURNS integer
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM public.documents);
END;
$$;
```

--------------------------------

### Drop Materialized View Statement

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/dependency/issue_444_drop_column_with_dependent_view/plan.txt

Example of dropping a materialized view. The RESTRICT clause is used to ensure no dependent objects are affected.

```sql
DROP MATERIALIZED VIEW IF EXISTS foo_mv RESTRICT;
```

--------------------------------

### Edit schema file

Source: https://github.com/pgplex/pgschema/blob/main/README.md

Declaratively edit the schema SQL file. This example shows adding a new column to the 'users' table.

```diff
---
a/schema.sql
+++
a/schema.sql
@@ -12,5 +12,6 @@
 
 CREATE TABLE IF NOT EXISTS users (
     id SERIAL PRIMARY KEY,
-    username varchar(50) NOT NULL UNIQUE
+    username varchar(50) NOT NULL UNIQUE,
+    age INT NOT NULL
 );
```

--------------------------------

### Go Build with Version Info

Source: https://github.com/pgplex/pgschema/blob/main/CLAUDE.md

Builds the pgschema CLI tool with version information embedded, suitable for CI/Docker environments. Requires Git commit and build date to be provided.

```bash
go build -ldflags="-w -s -X github.com/pgplex/pgschema/cmd.GitCommit=... -X 'github.com/pgplex/pgschema/cmd.BuildDate=...'" -o pgschema .
```

--------------------------------

### Introspect Live PostgreSQL Database with `ir`

Source: https://github.com/pgplex/pgschema/blob/main/ir/README.md

Connect to a PostgreSQL database using `database/sql` and the `lib/pq` driver. Then, create an `ir.Inspector` to build an Intermediate Representation (IR) of a specified schema.

```go
import (
    "context"
    "database/sql"
    "github.com/pgplex/pgschema/ir"
    _ "github.com/lib/pq"
)

// Connect to database
db, err := sql.Open("postgres", "postgresql://user:pass@localhost/dbname?sslmode=disable")
if err != nil {
    log.Fatal(err)
}
deferr db.Close()

// Create inspector with no ignore config
inspector := ir.NewInspector(db, nil)

// Build IR from database for "public" schema
ctx := context.Background()
schema, err := inspector.BuildIR(ctx, "public")
if err != nil {
    log.Fatal(err)
}

// Access normalized schema data
if publicSchema, ok := schema.GetSchema("public"); ok {
    fmt.Printf("Found %d tables\n", len(publicSchema.Tables))
}
```

--------------------------------

### Preview Tenant Schema Changes

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/tenant-schema.mdx

Generate migration plans for each tenant to see what changes will be applied before execution. Review the plans to ensure the changes are expected and safe for all tenants.

```bash
# Create a list of all tenant schema names
echo "tenant_001
tent_002
tent_003
tent_test" > tenant_list.txt

# Preview changes for a single tenant
mkdir -p plans
pgschema plan --host localhost --db myapp --user postgres \
  --schema tenant_001 --file tenant_schema.sql \
  --output-json "plans/tenant_001_plan.json" \
  --output-human "plans/tenant_001_plan.txt"

# Generate plans for all tenants (optional but recommended)
while read tenant; do
  echo "=== Plan for $tenant ==="
  pgschema plan --host localhost --db myapp --user postgres \
    --schema "$tenant" --file tenant_schema.sql \
    --output-json "plans/${tenant}_plan.json" \
    --output-human "plans/${tenant}_plan.txt"
done < tenant_list.txt
```

--------------------------------

### Alter Policy USING Clause

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/create_policy/alter_policy_using/plan.txt

Modifies the USING clause of an existing policy on the 'users' table. This example sets the tenant_id condition to 2.

```sql
ALTER POLICY user_tenant_isolation ON users USING (tenant_id = 2);
```

--------------------------------

### Initial Manus Prompt for CLI Demo

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/demo-with-manus-and-asciinema.mdx

The first instruction given to Manus to record a demo of pgschema's dump, edit, plan, and apply workflow using asciinema.

```text
Read https://www.pgschema.com/.

Use asciinema to record a cast to demonstrate. Dump, edit, plan, apply workflow 

```

--------------------------------

### Cross-Schema Foreign Key Reference

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

Example SQL demonstrating how to create a foreign key constraint that references a table in a different schema (`auth.users`).

```sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES auth.users(id),  -- Cross-schema FK works
  total DECIMAL(10,2)
);
```

--------------------------------

### Generate Production Deployment Plan

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Before deploying to production, generate a comprehensive plan that details all schema changes. Thoroughly review this plan to ensure accuracy and safety.

```bash
# Generate production deployment plan
pgschema plan --host prod.db.com --db myapp --user postgres \
  --file schema/main.sql \
  --output-json prod_plan.json \
  --output-human prod_plan.txt

# Review production plan thoroughly
less prod_plan.txt
```

--------------------------------

### Example Trigger on an Ignored Table

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/ignore.mdx

This SQL shows a trigger defined on a table that might be ignored by pgschema. The trigger itself will be managed even if the table is not.

```sql
-- schema.sql
CREATE TRIGGER on_data_change
  AFTER INSERT ON external_users
  FOR EACH ROW
  EXECUTE FUNCTION sync_data();
```

--------------------------------

### Generate initial migration plan

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/pgschema-postgres-declarative-schema-migration-like-terraform.mdx

This command generates a migration plan by comparing the desired schema defined in `schema.sql` against the current state of the database connected via the provided host, database, and user credentials. The plan is saved to `plan.json`.

```bash
pgschema plan \
  --host prod.db.com \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-json plan.json
```

--------------------------------

### Assigning Schema Ownership with CODEOWNERS

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/pgschema-postgres-declarative-schema-migration-like-terraform.mdx

Example of using GitHub's CODEOWNERS file to assign specific schema components to different teams for granular ownership.

```gitconfig
# .github/CODEOWNERS
schema/tables/users*           @user-team
schema/tables/orders*          @orders-team  
schema/tables/products*        @inventory-team
schema/views/user_*            @user-team
schema/views/order_*           @orders-team
schema/functions/*inventory*   @inventory-team
```

--------------------------------

### Standard Go Build

Source: https://github.com/pgplex/pgschema/blob/main/CLAUDE.md

Builds the pgschema CLI tool using the standard Go build command.

```bash
go build -o pgschema .
```

--------------------------------

### Revoke Default Privileges

Source: https://github.com/pgplex/pgschema/blob/main/docs/syntax/alter_default_privileges.mdx

Example of revoking previously granted default privileges on tables for specific roles. This is commonly generated during schema migrations to remove access.

```sql
-- Remove all default privileges
-- Before:
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO app_user;
-- After: (no default privileges)
-- Migration generates:
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE DELETE, INSERT, UPDATE ON TABLES FROM app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM readonly_user;
```

--------------------------------

### Concurrent Index Creation and Monitoring

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/pgschema-postgres-declarative-schema-migration-like-terraform.mdx

Demonstrates generating a migration for concurrent index creation and monitoring its progress using a built-in pgschema directive. This ensures minimal downtime during index operations.

```sql
-- Generated migration for index addition
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_status ON users (email, status DESC);

-- pgschema:wait - Built-in progress monitoring
SELECT 
    COALESCE(i.indisvalid, false) as done,
    CASE 
        WHEN p.blocks_total > 0 THEN p.blocks_done * 100 / p.blocks_total
        ELSE 0
    END as progress
FROM pg_class c
LEFT JOIN pg_index i ON c.oid = i.indexrelid
LEFT JOIN pg_stat_progress_create_index p ON c.oid = p.index_relid
WHERE c.relname = 'idx_users_email_status';
```

--------------------------------

### Deploy to Staging Environment

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/local-to-production.mdx

Apply schema changes to the staging environment using the generated plan.json.

```bash
pgschema apply --host staging.db.com --db myapp --user staging --plan plan.json
```

--------------------------------

### Run pgschema with Docker

Source: https://github.com/pgplex/pgschema/blob/main/docs/installation.mdx

Use Docker to run pgschema commands without local installation. Pull the latest image and execute commands using `docker run`.

```bash
# Pull the latest image
docker pull pgplex/pgschema:latest

# Run pgschema commands
docker run --rm pgplex/pgschema:latest --help
```

--------------------------------

### Apply Schema Changes (File Mode)

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/apply.mdx

Generates a migration plan from a desired state SQL file, displays it, and prompts for confirmation before applying changes. Useful for interactive schema updates.

```bash
# Interactive mode - shows plan and prompts for confirmation
pgschema apply --host localhost --db myapp --user postgres --file desired_schema.sql
```

--------------------------------

### SQL Function Dependency Example

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Illustrates a common dependency where a SQL function's return type references a table. The referenced table must exist before the function is created.

```sql
CREATE FUNCTION accept_extracted_recipes(recipe_ids uuid[])
RETURNS SETOF recipe_user_save   -- depends on the recipe_user_save table existing
LANGUAGE plpgsql AS $$ ... $$;
```

--------------------------------

### Environment Variables for Target and Plan Databases (.env)

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

Configuration for both the target application database and the external plan database using a `.env` file. This is the recommended approach for managing connection details.

```bash
# Target database connection
PGHOST=localhost
PGPORT=5432
PGDATABASE=myapp
PGUSER=postgres
PGPASSWORD=mypassword

# Plan database connection (optional)
PGSCHEMA_PLAN_HOST=localhost
PGSCHEMA_PLAN_PORT=5432
PGSCHEMA_PLAN_DB=pgschema_plan
PGSCHEMA_PLAN_USER=postgres
PGSCHEMA_PLAN_PASSWORD=planpassword
PGSCHEMA_PLAN_SSLMODE=prefer

# Run plan with external database
pgschema plan --file schema.sql

# Or apply (File Mode) with external database
pgschema apply --file schema.sql --auto-approve
```

--------------------------------

### Use Generated SQL Queries for Introspection

Source: https://github.com/pgplex/pgschema/blob/main/ir/README.md

Utilize pre-generated SQL queries from the `ir/queries` package to introspect database schemas. This example shows how to fetch tables for a specific schema.

```go
import "github.com/pgplex/pgschema/ir/queries"

q := queries.New(db)
tables, err := q.GetTables(ctx, "public")
```

--------------------------------

### Run Include Functionality Tests

Source: https://github.com/pgplex/pgschema/blob/main/testdata/include/README.md

Execute the integration tests for the include functionality using the go test command.

```bash
# Run include functionality tests
go test -v ./cmd/ -run "TestIncludeIntegration"
```

--------------------------------

### Run specific test cases with filter

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/README.md

Execute specific test cases using the PGSCHEMA_TEST_FILTER environment variable. This example filters for 'create_table/' tests in both diff and cmd packages.

```bash
# Run specific test cases with filter
PGSCHEMA_TEST_FILTER="create_table/" go test -v ./internal/diff -run TestDiffFromFiles
PGSCHEMA_TEST_FILTER="create_table/" go test -v ./cmd -run TestPlanAndApply
```

--------------------------------

### Combining Folder and File Includes

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Shows how to mix folder includes with specific file includes in a main SQL schema file.

```sql
-- main.sql - mix folder and file includes
\i core/
\i modules/auth/auth.sql
\i modules/reporting/

-- core/ folder contains individual files that get included alphabetically
-- modules/reporting/ folder gets all files included recursively
```

--------------------------------

### Alter Default Privileges for Tables and Sequences

Source: https://github.com/pgplex/pgschema/blob/main/docs/syntax/alter_default_privileges.mdx

Example of altering default privileges by revoking sequence privileges and granting additional table privileges. This is often generated during schema migrations.

```sql
-- Expand table privileges and remove sequence privileges
-- Before:
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO app_user;
-- After:
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;
-- Migration generates:
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE USAGE ON SEQUENCES FROM app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE ON TABLES TO app_user;
```

--------------------------------

### Verify Deployment Success

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Dumps the current schema from a production database into multiple files for verification. Creates a 'verification' directory to store the dumped schema.

```bash
mkdir -p verification/
pgschema dump --host prod.db.com --db myapp --user postgres \
  --multi-file --file verification/main.sql
```

--------------------------------

### CREATE SEQUENCE Syntax

Source: https://github.com/pgplex/pgschema/blob/main/docs/syntax/create_sequence.mdx

The full syntax for creating a sequence, including all optional parameters and their possible values.

```sql
create_sequence ::= CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
                    [ AS data_type ]
                    [ INCREMENT [ BY ] increment ]
                    [ MINVALUE minvalue | NO MINVALUE ] 
                    [ MAXVALUE maxvalue | NO MAXVALUE ]
                    [ START [ WITH ] start ]
                    [ CACHE cache ]
                    [ [ NO ] CYCLE ]
                    [ OWNED BY { table_name.column_name | NONE } ]

sequence_name ::= [schema.]name

data_type ::= smallint | integer | bigint

increment ::= integer

minvalue ::= integer

maxvalue ::= integer  

start ::= integer

cache ::= integer
```

--------------------------------

### Verify Deployment Across All Tenants

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/tenant-schema.mdx

Confirm all tenant schemas have been updated to match the desired template state. This involves dumping each tenant's schema and comparing it against the template.

```bash
# Create verification directory
mkdir -p verification

# Dump all tenant schemas
while read tenant; do
  echo "Verifying $tenant..."
  pgschema dump --host localhost --db myapp --user postgres \
    --schema "$tenant" > "verification/${tenant}.sql"
done < tenant_list.txt

# Compare each tenant schema against the template
while read tenant; do
  diff tenant_schema.sql "verification/${tenant}.sql" || {
    echo "❌ Schema mismatch detected in $tenant - does not match template"
    exit 1
  }
  echo "✅ $tenant matches template schema"
done < tenant_list.txt

echo "✅ All tenant schemas match the desired template state"
```

--------------------------------

### Multiple Output Formats for Documentation and Automation

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan.mdx

Generates schema plans in multiple formats simultaneously, including human-readable text, JSON for automation, and SQL for migrations. One output can be directed to stdout.

```bash
# Save all formats for comprehensive documentation
pgschema plan \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-human plan.txt \
  --output-json plan.json \
  --output-sql migration.sql

# Display human-readable format and save JSON for automation
pgschema plan \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-human stdout \
  --output-json automation/plan.json \
  --no-color
```

--------------------------------

### Connection using Command Line Flag

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/apply.mdx

Provides the database password directly via the --password command-line flag.

```bash
pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--password mypassword \
--file schema.sql
```

--------------------------------

### Example Schema-Level Dump Output

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/pgschema-postgres-declarative-schema-migration-like-terraform.mdx

This SQL output represents a schema-level dump generated by pgschema. It is schema-agnostic, meaning it removes schema qualifiers from table and index names and object references, making the DDL portable.

```sql
--
-- pgschema database dump
--

--
-- Name: users; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username varchar(100) NOT NULL,
    email varchar(100) NOT NULL,
    role public.user_role DEFAULT 'user',
    status public.status DEFAULT 'active',
    created_at timestamp DEFAULT now()
);

--
-- Name: posts; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    user_id integer REFERENCES users(id),
    title varchar(200) NOT NULL,
    content text,
    created_at timestamp DEFAULT now()
);

```

--------------------------------

### Create products Table

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/create_table/add_table_like/plan.txt

Defines the 'products' table with serial ID, timestamp columns, and a primary key.

```sql
CREATE TABLE IF NOT EXISTS products (
    id SERIAL,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    deleted_at timestamptz,
    CONSTRAINT products_pkey PRIMARY KEY (id)
);
```

--------------------------------

### Create PostgreSQL Function for Validation

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/create_domain/domain_function_check_dependency/plan.txt

This function validates if a given text value meets specific criteria (starts with 'id_' and is at least 5 characters long). It's used by the domain's CHECK constraint.

```SQL
CREATE OR REPLACE FUNCTION validate_custom_id(
    val text
)
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  -- Simple validation: must start with 'id_' and be at least 5 characters
  RETURN val IS NOT NULL AND val LIKE 'id_%' AND length(val) >= 5;
END
$$;
```

--------------------------------

### Dump current database schema

Source: https://github.com/pgplex/pgschema/blob/main/docs/blog/pgschema-postgres-declarative-schema-migration-like-terraform.mdx

Use `pgschema dump` to extract the current schema definition from a live PostgreSQL database. This is useful for creating a baseline schema file when recovering from a fingerprint mismatch or for initial setup.

```bash
# 1. Get the latest schema from the current database
pgschema dump \
  --host prod.db.com \
  --db myapp \
  --user postgres \
  > current-schema.sql
```

--------------------------------

### Deploy to Production Environment

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/local-to-production.mdx

Apply schema changes to the production environment using the same plan.json. Use `--auto-approve` for automated deployments.

```bash
# Apply with plan (add [`--auto-approve`](/cli/apply#param-auto-approve) for automated deployments)
pgschema apply --host prod.db.com --db myapp --user prod --plan plan.json
```

--------------------------------

### Plan Command with All Options as Flags

Source: https://github.com/pgplex/pgschema/blob/main/docs/cli/plan-db.mdx

Executing the `pgschema plan` command using only command-line flags for all connection and configuration options. This approach avoids the need for environment variables.

```bash
# Plan command - all options as flags (no environment variables)
pgschema plan \
  --file schema.sql \
  --host localhost \
  --db myapp \
  --user postgres \
  --password mypassword \
  --plan-host localhost \
  --plan-db pgschema_plan \
  --plan-user postgres \
  --plan-password planpassword
```

--------------------------------

### Folder Includes in main.sql

Source: https://github.com/pgplex/pgschema/blob/main/docs/workflow/modular-schema-files.mdx

Demonstrates using the \i directive with a trailing slash to include all .sql files within a folder and its subdirectories recursively, processed in alphabetical order.

```sql
-- Include all types (processed alphabetically: address, order_status, user_status)
\i types/

-- Include all tables (processed alphabetically: orders, users)
\i tables/

-- Include all functions recursively (auth/validate_user, calculate_total, update_timestamp)
\i functions/
```

--------------------------------

### Run Unit Tests for PgSchema

Source: https://github.com/pgplex/pgschema/blob/main/README.md

Execute only the unit tests for the PgSchema project using the 'go test -short -v ./...' command.

```bash
# Run unit tests only
go test -short -v ./...
```

--------------------------------

### Create Table: activity_x

Source: https://github.com/pgplex/pgschema/blob/main/testdata/diff/dependency/issue_300_view_depends_on_view/plan.txt

Defines the 'activity_x' table with an ID, title, and a foreign key to priority_user.

```sql
CREATE TABLE IF NOT EXISTS activity_x (
    id integer,
    title text NOT NULL,
    priority_user_id integer,
    CONSTRAINT activity_x_pkey PRIMARY KEY (id)
);
```