Use check constraints for setting NOT NULL columns in PostgreSQL

postgresqldrizzlemigrations
When adding a NOT NULL constraint to an existing column in PostgreSQL, use a check constraint first to avoid blocking reads and writes while every row is checked.
Bad:
```sql
-- This can cause performance issues with large tables
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
```

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-set-column-not-null | bash

Manual install

1

Copy the rule

---
include: *.sql
---
When adding a NOT NULL constraint to an existing column in PostgreSQL, use a check constraint first to avoid blocking reads and writes while every row is checked.
Bad:
```sql
-- This can cause performance issues with large tables
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
```
Good:
```sql
-- Step 1: Add a check constraint without validation
ALTER TABLE users
ADD CONSTRAINT users_some_column_null CHECK (some_column IS NOT NULL) NOT VALID;
-- Step 2: In a separate transaction, validate the constraint
ALTER TABLE users
VALIDATE CONSTRAINT users_some_column_null;
-- Step 3: Add the NOT NULL constraint and remove the check constraint
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
ALTER TABLE users
DROP CONSTRAINT users_some_column_null;
```
2

Add the rule into your project

Save the copied content as: .wispbit/rules/postgresql-set-column-not-null.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 a NOT NULL constraint to an existing column in PostgreSQL, use a check constraint first to avoid blocking reads and writes while every row is checked.
        
        Bad:
        
        ```sql
        -- This can cause performance issues with large tables
        ALTER TABLE users
        ALTER COLUMN some_column SET NOT NULL;
        ```
        
        Good:
        
        ```sql
        -- Step 1: Add a check constraint without validation
        ALTER TABLE users
        ADD CONSTRAINT users_some_column_null CHECK (some_column IS NOT NULL) NOT VALID;
        
        -- Step 2: In a separate transaction, validate the constraint
        ALTER TABLE users
        VALIDATE CONSTRAINT users_some_column_null;
        
        -- Step 3: Add the NOT NULL constraint and remove the check constraint
        ALTER TABLE users
        ALTER COLUMN some_column SET NOT NULL;
        
        ALTER TABLE users
        DROP CONSTRAINT users_some_column_null;
        ```

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 a NOT NULL constraint to an existing column in PostgreSQL, use a check constraint first to avoid blocking reads and writes while every row is checked.
Bad:
```sql
-- This can cause performance issues with large tables
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
```
Good:
```sql
-- Step 1: Add a check constraint without validation
ALTER TABLE users
ADD CONSTRAINT users_some_column_null CHECK (some_column IS NOT NULL) NOT VALID;
-- Step 2: In a separate transaction, validate the constraint
ALTER TABLE users
VALIDATE CONSTRAINT users_some_column_null;
-- Step 3: Add the NOT NULL constraint and remove the check constraint
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
ALTER TABLE users
DROP CONSTRAINT users_some_column_null;
```

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 a NOT NULL constraint to an existing column in PostgreSQL, use a check constraint first to avoid blocking reads and writes while every row is checked.
Bad:
```sql
-- This can cause performance issues with large tables
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
```
Good:
```sql
-- Step 1: Add a check constraint without validation
ALTER TABLE users
ADD CONSTRAINT users_some_column_null CHECK (some_column IS NOT NULL) NOT VALID;
-- Step 2: In a separate transaction, validate the constraint
ALTER TABLE users
VALIDATE CONSTRAINT users_some_column_null;
-- Step 3: Add the NOT NULL constraint and remove the check constraint
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
ALTER TABLE users
DROP CONSTRAINT users_some_column_null;
```

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 a NOT NULL constraint to an existing column in PostgreSQL, use a check constraint first to avoid blocking reads and writes while every row is checked.
Bad:
```sql
-- This can cause performance issues with large tables
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
```
Good:
```sql
-- Step 1: Add a check constraint without validation
ALTER TABLE users
ADD CONSTRAINT users_some_column_null CHECK (some_column IS NOT NULL) NOT VALID;
-- Step 2: In a separate transaction, validate the constraint
ALTER TABLE users
VALIDATE CONSTRAINT users_some_column_null;
-- Step 3: Add the NOT NULL constraint and remove the check constraint
ALTER TABLE users
ALTER COLUMN some_column SET NOT NULL;
ALTER TABLE users
DROP CONSTRAINT users_some_column_null;
```

File Path Patterns:

*.sql