Split unique constraints in PostgreSQL

postgresqldrizzlemigrations
When adding unique constraints in PostgreSQL, create the unique index concurrently first before adding the constraint to avoid blocking reads and writes.
Bad:
```sql
-- Creates a unique constraint directly, which blocks reads and writes
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
```

Install this rule for wispbit

Quick Install

Recommended
View install script

Run this one command to automatically install the rule:

curl -fsSL https://wispbit.com/api/install?rule=postgresql-unique-constraint | bash

Manual install

1

Copy the rule

---
include: *.sql
---
When adding unique constraints in PostgreSQL, create the unique index concurrently first before adding the constraint to avoid blocking reads and writes.
Bad:
```sql
-- Creates a unique constraint directly, which blocks reads and writes
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
```
Good:
```sql
-- First create a unique index concurrently (non-blocking)
CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (email);
-- Then add the constraint using the existing index
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx;
```
2

Add the rule into your project

Save the copied content as: .wispbit/rules/postgresql-unique-constraint.md

Install this rule for Coderabbit

Copy the configuration below and add it to your repository as .coderabbit.yml in your project root.

reviews:
  path_instructions:
    - path: "*.sql"
      instructions: |
                
        When adding unique constraints in PostgreSQL, create the unique index concurrently first before adding the constraint to avoid blocking reads and writes.
        
        Bad:
        
        ```sql
        -- Creates a unique constraint directly, which blocks reads and writes
        ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
        ```
        
        Good:
        
        ```sql
        -- First create a unique index concurrently (non-blocking)
        CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (email);
        
        -- Then add the constraint using the existing index
        ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx;
        ```

Install this rule for Greptile

Greptile rules can be added through the web interface. Please see this documentation for details on how to add custom rules and context.

When adding unique constraints in PostgreSQL, create the unique index concurrently first before adding the constraint to avoid blocking reads and writes.
Bad:
```sql
-- Creates a unique constraint directly, which blocks reads and writes
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
```
Good:
```sql
-- First create a unique index concurrently (non-blocking)
CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (email);
-- Then add the constraint using the existing index
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx;
```

File Path Patterns:

*.sql

Install this rule for GitHub Copilot

Copilot instructions can be added through the interface. See the documentation for details on how to create coding guidelines.

When adding unique constraints in PostgreSQL, create the unique index concurrently first before adding the constraint to avoid blocking reads and writes.
Bad:
```sql
-- Creates a unique constraint directly, which blocks reads and writes
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
```
Good:
```sql
-- First create a unique index concurrently (non-blocking)
CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (email);
-- Then add the constraint using the existing index
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx;
```

File Path Patterns:

*.sql

Install this rule for Graphite Diamond

Diamond custom rules can be added through the interface. See the documentation for details on how to create custom rules.

When adding unique constraints in PostgreSQL, create the unique index concurrently first before adding the constraint to avoid blocking reads and writes.
Bad:
```sql
-- Creates a unique constraint directly, which blocks reads and writes
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
```
Good:
```sql
-- First create a unique index concurrently (non-blocking)
CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (email);
-- Then add the constraint using the existing index
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx;
```

File Path Patterns:

*.sql